Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Friends,
kindly find the attachment excel which i described my issue.
Need to compare current row record with previous row.
May be like this?
Directory;
T1:
LOAD
ID1,
[Meter Code1],
TimeStamp1
FROM
week_inflow.xls
(biff, embedded labels, table is Sheet1$) ;
NoConcatenate
LOAD ID1,
[Meter Code1],
TimeStamp1,
if([Meter Code1]=Previous([Meter Code1]),1,0) as Flag
Resident T1 Order by [Meter Code1];
DROP Table T1;
are you sure about the flag column on the "expected output" ? i think it is the opposite of what you described
yes . i want that expected output table.
"I want to create new column called flag. If meter code is present in previous timestamp , then flag is 0, else 1"
the meter code: 6032015092 was present on a previous TimeStamp, on the line 6 of the left table, so the flag should take 1 and you put it to 0, how can you explain that ?
no. that meter id is newly created on the current timestamp, so it should came as 0. if that meter already present in previous timestamp then it should be 1.
yes that is right. it should be mentioned as 0. i wrongly mentioned as 1
May be like this?
Directory;
T1:
LOAD
ID1,
[Meter Code1],
TimeStamp1
FROM
week_inflow.xls
(biff, embedded labels, table is Sheet1$) ;
NoConcatenate
LOAD ID1,
[Meter Code1],
TimeStamp1,
if([Meter Code1]=Previous([Meter Code1]),1,0) as Flag
Resident T1 Order by [Meter Code1];
DROP Table T1;
exactly what i want. thank you very much.
Welcome . Can you close this thread, if you got an answer ..
A:
LOAD * INLINE [
ID1, Meter Code, TimeStamp
1, 143000192, 10/2/2017
1, 143000280, 10/2/2017
1, 1162000015, 10/2/2017
1, 6032014586, 10/2/2017
1, 6032015092, 10/2/2017
1, 6032015347, 10/2/2017
1, 6032015422, 10/2/2017
1, 6032015505, 10/2/2017
1, 6032707190, 10/2/2017
2, 143000186, 10/9/2017
2, 6032015092, 10/9/2017
2, 6032015347, 10/9/2017
2, 6032015422, 10/9/2017
2, 6032015505, 10/9/2017
2, 6032707600, 10/9/2017
2, 6032709578, 10/9/2017
];
left join(A)
LOAD
[Meter Code],
TimeStamp,
if([Meter Code] = Previous( [Meter Code]) and Previous(TimeStamp),0,1) as new_peek
Resident A
Order by [Meter Code],
TimeStamp;