How to group rows searching by operators (and/or).

[Danielecc]

I would like to know how i can search rows in other tables using some criteria with operators "AND/OR" in each row by group.

In table "Criteria" I have the criteria that i need to find in table "Sales" and table "Seller":

Step 1. In table "Criteria" make a Group by using column City and Area to get the "lines to search" (Considerating operators AND/OR in the 'group by'). And the range From-To is OK if they get match in at last one of the values in range.

Step 2. Search in table "Sales" with table "Seller" if anyone (seller) match all the conditions (AND/OR) and range From-To (at las one) of the Step 1.

Step 3. Create a table resume only with Sellers who get all the criteria and/or of the "group by" and "at last one" of the specified ranges From-To where the result expected is: Seller, City and Codes from who get all the conditions of a group by.

The colors are only a visual help to understand the relationship between the 3 tables and I use yellow like a mix of both colors from talble Criteria.

Then, the result expected is a new table with:

This seller get all the criteria with this codes considerating "From-To" ranges (values) and considerating and/or "criteria".

I will copy and Paste in text mode each table.

Table Criteria:

City Area Organization Item From To Operator
N.Y.1 NY_B2 Company_T1 Quantity 3 7 AND
N.Y.1 NY_B2 Company_T1 Product PRD03 PRD06 OR
N.Y.1 NY_B2 Company_T1 Product PRD15 PRD18 OR
N.Y.1 NY_B2 Company_B5 Quantity 1 3 AND
N.Y.1 NY_B2 Company_B5 Product PRD04 PRD06 OR
N.Y.1 NY_B2 Company_B5 Product PRD09 PRD11 OR
N.Y.2 NY_C3 Company_A1 Quantity 2 4 AND
N.Y.2 NY_C3 Company_A1 Long LA2 LA5 AND
N.Y.2 NY_C3 Company_A1 Product PRD02 PRD05 AND
N.Y.2 NY_C3 Company_B2 Quantity 3 5 AND

Table Sales:

CODE Organization Item From To
EEUU01 Company_A1 Quantity 3 3
EEUU01 Company_A1 Long LA3 LA5
EEUU02 Company_A1 Product PRD03 PRD05
EEUU02 Company_B2 Quantity 2 2
EEUU02 Company_B5 Product PRD03 PRD05
EEUU02 Company_T1 Quantity 2 2
EEUU03 Company_T1 Quantity 4 4
EEUU04 Company_T1 Product PRD17 PRD19
EEUU04 Company_T1 Product PRD20 PRD22
EEUU05 Company_T1 Product PRD04 PRD04
EEUU05 Company_B5 Product PRD12 PRD12
EEUU06 Company_A1 Quantity 3 3
EEUU06 Company_A1 Long LA3 LA4
EEUU06 Company_B2 Long LA3 LA7
EEUU07 Company_B2 Quantity 1 1
EEUU08 Company_T1 Product PRD04 PRD04
EEUU09 Company_B2 Quantity 3 3
EEUU09 Company_A1 Long LA6 LA6
EEUU10 Company_T1 Product PRD16 PRD16
EEUU10 Company_T1 Product PRD12 PRD12
EEUU11 Company_A1 Long LA1 LA1
EEUU11 Company_B5 Product PRD09 PRD10
EEUU12 Company_A1 Product PRD01 PRD06
EEUU13 Company_B5 Quantity 2 2
EEUU14 Company_B5 Quantity 1 1
EEUU15 Company_B5 Product PRD01 PRD03

Table Seller:

Seller With CODE
D. Celis EEUU01
D. Celis EEUU02
D. Celis EEUU03
D. Celis EEUU05
J. Perez EEUU01
J. Perez EEUU02
J. Perez EEUU07
J. Perez EEUU03
J. Perez EEUU05
J. Perez EEUU13
J. Perez EEUU02
S. Gomez EEUU06
S. Gomez EEUU12
S. Gomez EEUU09
S. Gomez EEUU03
S. Gomez EEUU10
S. Gomez EEUU13
S. Gomez EEUU15
J.Smith EEUU01
J.Smith EEUU02
J.Smith EEUU09
J.Smith EEUU03
J.Smith EEUU05
J.Smith EEUU15
J.Smith EEUU07
P. Court EEUU01
P. Court EEUU02
P. Court EEUU07
P. Court EEUU03
P. Court EEUU04
P. Court EEUU14
P. Court EEUU11
S. Kent EEUU06
S. Kent EEUU12
S. Kent EEUU09
S. Kent EEUU03
S. Kent EEUU08
S. Kent EEUU14
S. Kent EEUU11
P. Mcay EEUU01
P. Mcay EEUU02
P. Mcay EEUU07
P. Mcay EEUU04
P. Mcay EEUU05

Thanks for your help with this difficult case.

[v-jingzhang]

Hi @Danielecc 

Sorry I have difficulty understanding the relationship between Table Sales and Table Seller. For example, in Table Seller, the pink colored EEUU01 has 5 sellers while in the result table, it only has J. Smith. I don't understand how it filters or searches in Table Seller to get this result. Can you help explain how it should search?

Best Regards,
Community Support Team _ Jing

[Danielecc]

Hi @v-jingzhang thanks for see my post, I am sure is a very difficult case.

Only J.Smith is in table result because she get all the conditions from City N.Y.2 of table "criteria", but not only for EEUU01, she got City N.Y.2 because she has assigned the codes EEUU01, EEUU02 and EEUU09. With this 3 codes she got compliance all the criteria of City N.Y.2 from table criteria with at last one value in the range From/To.

With EEUU01 she got at last one value between the ranges :

Company_A1 with Quantity, value = 3 AND Company_A1 with Long,  values = LA3 to LA5

With EEUU02 she got at last one value between the ranges: 
Company_A1 with Product, values = PRD03 to PRD05

With EEUU09 she got at last one value between the ranges:
Company_B2 with Quantity, value = 3

To be in the "table expected" the seller must get compliance all the criteria (AND/OR) of the City from table Criteria. The green colored is more difficult because has rows with "AND" and others rows with "OR".

I hope this explanation can help you to help me   🙂  

Best regards and thanks again...

[Greg_Deckler]

@Danielecc What does the phrase "at last one" mean? Is that supposed to be "at least one"?

[Danielecc]

Hi @Greg_Deckler , thanks a lot for see this case (my English is very bad, sorry)

Fox Example in table Criteria we have this row:

N.Y.1 ; NY_B2 ; Company_T1 ; Product ; PRD03 ; PRD06 ; OR

Then, the range From/to is: PRD03, PRD04, PRD05, PRD06.

If Greg sell to Company_T1 the Product PRD04 then this row is OK, because Greg sell "at last one" of the values in range.

If Greg Sell to Company_T1 the Product PRD05, PRD06 and PRD07 then this row is OK, even if PRD07 is out of range because PRD05 and PRD06 are in range of the product range.

Thanks Again for your help in this very difficult case (and for my english).

Best regards.