Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
praveenkumar_s
Creator II
Creator II

Get week inflow

Friends,

kindly find the attachment excel which i described my issue.

Need to compare current row record with previous row.

1 Solution

Accepted Solutions
settu_periasamy
Master III
Master III

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;

View solution in original post

11 Replies
YoussefBelloum
Champion
Champion

are you sure about the flag column on the "expected output"  ? i think it is the opposite of what you described

praveenkumar_s
Creator II
Creator II
Author

yes . i want that expected output table.

YoussefBelloum
Champion
Champion

"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 ?

praveenkumar_s
Creator II
Creator II
Author

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.

praveenkumar_s
Creator II
Creator II
Author

yes that is right. it should be mentioned as 0. i wrongly mentioned as 1

settu_periasamy
Master III
Master III

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;

praveenkumar_s
Creator II
Creator II
Author

exactly what i want. thank you very much.

settu_periasamy
Master III
Master III

Welcome . Can you close this thread, if you got an answer ..

devarasu07
Master II
Master II

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;

Capture.JPG