Next Row as New Column and Last of a Column

[Sai33]

Hello All,

I have my Source data in the following format

ID	Loc	Entry	Exit
1	A	12.01.2020	13.01.2020
1	B	14.01.2020	16.01.2020
1	C	17.01.2020	19.01.2020

Expected Result

ID	Loc	Entry	Exit	Next Loc	Last Loc
1	A	12.01.2020	13.01.2020	B	C
1	B	14.01.2020	16.01.2020	C	C
1	C	17.01.2020	19.01.2020		C

I have tried using Peek and Previous functions for the Next Loc. But, wasn't somehow getting the desired result. And not sure how to deal with the last Value.

Any pointers would be highly useful.

TIA!

[Taoufiq_Zarra]

maye be :

Data:
LOAD rowNo() as IDtmp,* INLINE [
    ID, Loc, Entry, Exit
    1, A, 12.01.2020, 13.01.2020
    1, B, 14.01.2020, 16.01.2020
    1, C, 17.01.2020, 19.01.2020
];

FirstSortedValue:

LOAD FirstSortedValue(Loc,-IDtmp) as LastLoc Resident Data Group By ID; 

LET LastLoc = peek('LastLoc');

drop table FirstSortedValue;

temps2:
noconcatenate
load *,'$(LastLoc)' as "Last Loc" resident Data order by IDtmp DESC;

drop table Data;

Result:
noconcatenate
load 
*,
Previous(Loc) as "Next Loc"
resident temps2;

drop table temps2;
drop field IDtmp;

output:

[Sai33]

Hi Taoufiq

This is working fine when there is only one ID as in my Example data. But, when there are multiple IDs it's always condisdering the Last LOC it should instead consider the Last LOC of the particular ID.

[Sai33]

ID	Loc	Entry	Exit
1	A	12.01.2020	13.01.2020
1	B	14.01.2020	16.01.2020
1	C	17.01.2020	19.01.2020
2	R	12.01.2020	13.01.2020
2	T	14.01.2020	16.01.2020

ID	Loc	Entry	Exit	Next Loc	Last Loc
1	A	12.01.2020	13.01.2020	B	C
1	B	14.01.2020	16.01.2020	C	C
1	C	17.01.2020	19.01.2020		C
2	R	12.01.2020	13.01.2020	T	T
2	T	14.01.2020	16.01.2020		T

[Taoufiq_Zarra]

Yes, I'm waiting for that remark 😁  You're right

can you share a sample data with # ID and the result

[Sai33]

Updated my Source data and Required Solution

[Sai33]

Thanks for your prompt reply. Let me know if the updated Sample data is enough for you to test. Or else i will create a test qvw and attach here.

But, the example data i have updated is exactly how my data looks like.

[Sai33]

Please, find the attached qvf along with Sample data and Expected result

[Taoufiq_Zarra · Accepted solution]

I just took a break 😁

Data0:
LOAD * INLINE [
ID,Loc,Entry,Exit
1,A,12.01.2020,13.01.2020
1,B,14.01.2020,16.01.2020
2,R,12.01.2020,13.01.2020
1,C,17.01.2020,19.01.2020
2 ,T,14.01.2020,16.01.2020
3 ,K,14.01.2020,16.01.2020
];


Data:
noconcatenate
LOAD rowNo() as IDtmp,* resident Data0 order by ID;

drop table Data0;
tmp:
noconcatenate
LOAD ID,FirstSortedValue(Loc,-IDtmp) as LastLoc Resident Data Group By ID; 

join load * resident Data order by ID;
drop table Data ;

tmp2:
noconcatenate
load * resident tmp  order by IDtmp DESC;

drop table tmp;

output:
load
*,
if(ID=Previous(ID),Previous(Loc)) as "Next Loc"
resident tmp2;
right join 
load * resident tmp2;

drop table tmp2;

output:

[Taoufiq_Zarra]

Have you tested the script ?