Compare rows in a table

[florinaturlea]

Hello,

I have the data as in the table below:

JobId	Date	Step 1	Step 2	Step 3
A	22.05	1	1	0
B	22.05	1	1	1
C	22.05	1	0	0
A	20.05	1	1	1
B	20.05	1	0	0
D	20.05	1	1	0

I need to analyze two things:

1. I have a list of JobId that are lost from 20.05 to 22.05 ( for example D existed in 20.05, but doesn't exist anymore in 22.05). I need to find a way to show what is the last step Job D was in. In this case, we can see that it is Step 2, meaning that Job D disappeared in Step 2.

2. For the common JobIds (those that are in both days, like A and B), I need to compare the steps they are in and see if the last reached step is the same. For example job A was in step 3 in 20.05, but in 22.05, is in step 2, meaning that has moved one step back.  In this case, I want to flag those sites whose steps have changed, what was the last reached step they were  2 days ago and what is the step they are in now.

Many thanks!

[brunobertels]

Hello 

Not sure it may help but try this 

//first transform data with a crosstable load : 

[temp]:
crosstable(Step,Value,2)
LOAD * INLINE
[
JobId,Date,Step 1,Step 2,Step 3
A,22.05,1,1,0
B,22.05,1,1,1
C,22.05,1,0,0
A,20.05,1,1,1
B,20.05,1,0,0
D,20.05,1,1,0
](delimiter is ',');

// reload data add a dimension RANK 
Table:
load *,
if(Value=1, right(Step,1),null()) as Rank
resident temp;
drop table temp;

then in table you can do that 

LAST STEP per JobID and Date : 

Mesure :

FirstSortedValue(Step,-aggr(max(if(Value=1, right(Step,1),null())),Date,JobId,Step))

PREVIOUS STEP per Job ID and Date :

FirstSortedValue(Step,-aggr(max(if(Value=1, right(Step,1),null()),-1),Date,JobId,Step),2)

table resulting 

see the app attached 

Hope it helps 

Regards