How Can I compare different rows?

[elenarelinque]

Hi! I need to compare between different rows, I have a list of production orders and I need to match the correlative ones. For example, this would be a match:

Correlative production orders (OF) 1782304 and 1782305, same phase (FASE) 710 and same quantity (CAN) 25.

The problem is when I have repetitive production orders (OF) 1784077 because those are in different phases (FASE) 560 and 710:

The ones that should match are those highlighted in green, because they have the same amount (CAN) and phase (FASE).

As I'm using this expression:

=If([ORDEN_DE_FABRICACION_R] = Above(TOTAL [ORDEN_DE_FABRICACION_R]+1),
If([FASE_R] = Above(TOTAL[FASE_R]),
If(Sum([CANTIDAD_R]) = Above(TOTAL Sum([CANTIDAD_R])),
'Completo','Parcial'),'Distinta fase'),
If([ORDEN_DE_FABRICACION_R] = Below(TOTAL [ORDEN_DE_FABRICACION_R]-1),
If([FASE_R] = Below(TOTAL[FASE_R]),
If(Sum([CANTIDAD_R]) = Below(TOTAL Sum([CANTIDAD_R])),
'Completo','Parcial'),'Distinta fase'),'No'))

It compares with the previous and the next row, but in this case there is a repeated OF in between. I don't know if it is possible to compare with two rows above, for example, or if it is possible to check for correlative OF in all rows not only in the row above/below  to solve this.

Thanks in advance.

[vinieme12]

Can you explain what exactly you are trying to evaluate here?

there are usually multiple ways to get things done so its better to focus on the result rather than A PARTICULAR METHOD

[elenarelinque]

Hi! Thanks for your answer. I need to match correlative production orders (OF) with the same phase (FASE) and quantity (CAN). If there is a total match I need to indicate 'Yes' in another column, 'Partial' if the OF are correlatives and the phase is the same, but the quantity doesn't match, 'Different phase' if the OF are correlatives, the quantity match but the phases are different, if the OF are no correlatives I need to indicate 'No' in the column.

Example 1: (OF) 1782304 and 1782305, same phase (FASE) 710 and same quantity (CAN) 25. This is a perfect match that should indicate 'Yes' in the column (this is working now):

Example 2: (OF) 1784076 and 1784077, same phase (FASE) 710 and same quantity (CAN) 67. This is a perfect match that should indicate 'Yes' in the column for the row 1 and 3, but for the row 2 should be a 'No', because there is no correlative OF with the same quantity and phase.

Thank you in advance!

[vinieme12]

try below

=if(Max(TOTAL <FASE> CAN)=CAN,'Yes','No')

[elenarelinque]

Hi! Thanks for your answer but I think I didn't explain myself properly. The conditions are:

- The production order need to match with a correlative one. For example, OF 1111112 and 1111113 is a match. This is the most important condition.

- Those production orders OF 1111112 and 1111113 should have the same PHASE to match.

- The last condition is the quantity.

To show a 'Yes' I need those 3 conditions.

[vinieme12]

is there a way to logically identify correlated OF's ;

for example is the first 5 digits of correlated OF's always the same?

IF yes

, then create a new field in your data model

left(OF,5) as OF_group

then use  =if(Max(TOTAL <OF_group> CAN)=CAN,'Yes','No')

[elenarelinque]

Not always, because OF 1111119 and 1111120 is a match, but OF 1111119 and 1111123 no. 

[vinieme12]

first 5 digits!! which is 11111    for 11111-19  and 11111-23

Is there any other way of confirming logically, not visually that these two are part of the same cohort

[elenarelinque]

11111-19  and 11111-23 are no correlatives so first 5 digits is not the solution. The only logic is that a prodution order match with other one if it is +1 or -1.

[AustinSpivey]

Here's something you can try:

If([OF] = Above(Total [OF]+1, Fabs([OF] = Above(TOTAL [OF]+1, 2)) + 1)
, If([FASE] = Above(Total [FASE], Fabs([OF] = Above(TOTAL [OF]+1, 2)) + 1)
, If([CANTIDAD] = Above(Total [CANTIDAD], Fabs([OF] = Above(TOTAL [OF]+1, 2)) + 1)
, 'Completo','Parcial'),'Distinta fase')

, If([OF] = Below(Total [OF]-1, Fabs([OF] = Below(TOTAL [OF]-1, 2)) + 1)
, If([FASE] = Below(Total [FASE], Fabs([OF] = Below(TOTAL [OF]-1, 2)) + 1)
, If([CANTIDAD] = Below(Total [CANTIDAD], Fabs([OF] = Below(TOTAL [OF]-1, 2)) + 1)
, 'Completo','Parcial'),'Distinta fase'),'No'))

The key here is the addition of these two expressions:

Fabs([OF] = Above(TOTAL [OF]+1, 2)) + 1
Fabs([OF] = Below(TOTAL [OF]-1, 2)) + 1

This part will return True if [OF] is equal to the [OF] value two records above/below the current one, with 1 added to it:

[OF] = Above(TOTAL [OF]+1, 2)

If it returns True, Qlik represents that numerically as negative one (-1). If false, it will represent it as a zero (0).

This next part uses the Fabs() function to get the absolute numeric value of the -1 returned in the previous expression. So if -1 is returned, Fabs() changes it to a positive one (1). Zero would just stay zero (0). Then, we add one to the result.

Fabs( ... ) + 1

We add that whole expression above as the second parameter of the Above() and Below() functions to indicate that if the [OF] from 2 records above or below equals [OF]+1, then we want to offset our Above() and Below() functions to calculate for that record. Otherwise, just calculate for the one directly above/below (offset of 1).

Here's a table that shows some sample data and the results of the original and new expressions:

OF	FASE	CANTIDAD	Original Expression	New Expression
1782153	560	1	No	No
1782301	560	30	No	No
1782304	710	25	Completo	Completo
1782305	710	25	Completo	Completo
1782308	580	39	No	No
1784076	710	67	Distinta fase	Completo
1784077	560	2	Distinta fase	Distinta fase
1784077	710	67	No	Completo

Now, ideally we could do something to dynamically do this in a way that aggregates on the [OF] field, as you could ostensibly have even more than 2 records in a row with the same [OF] value, so you'd want to do something that would check the last non-matching [OF] without specifically looking only 1 or 2 rows above.

Always room for improvement!