Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear community,
I have a table as in the picture with a reference, date when we import the dataset, and name (that change everyday)
I want to filter this table to keep only the value where In=1 but in the column name take the last value.
Could you please assist me ??
Thanks
T:
load * Inline[
Ref,Date,In,Out,Name
A1,01/05/2023,1,0,
A1,02/05/2023,0,0,V13
A1,03/05/2023,0,0,V16
A1,04/05/2023,0,0,P98
A1,05/05/2023,0,1,P98
B1,02/05/2023,1,0,
B1,05/05/2023,0,0,V55
B1,04/05/2023,0,0,G87
B1,05/05/2023,0,0,H34
];
NoConcatenate
F:
LOAD Ref,
LastValue(Name) as Name,
Date(Max(Date)) as Date
Resident T
Group By Ref;
Left Join
LOAD Ref,
In,
Out
Resident T
Where In = 1;
DROP Table T;
EXIT SCRIPT;
T:
load * Inline[
Ref,Date,In,Out,Name
A1,01/05/2023,1,0,
A1,02/05/2023,0,0,V13
A1,03/05/2023,0,0,V16
A1,04/05/2023,0,0,P98
A1,05/05/2023,0,1,P98
B1,02/05/2023,1,0,
B1,05/05/2023,0,0,V55
B1,04/05/2023,0,0,G87
B1,05/05/2023,0,0,H34
];
NoConcatenate
F:
LOAD Ref,
LastValue(Name) as Name,
Date(Max(Date)) as Date
Resident T
Group By Ref;
Left Join
LOAD Ref,
In,
Out
Resident T
Where In = 1;
DROP Table T;
EXIT SCRIPT;
Thank you so much