topic Re: Compare Previous Values and create new field in QlikView

Compare Previous Values and create new field

markgraham123 — Sat, 16 Nov 2024 03:28:44 GMT

Hi all,

I'm working on a requirement where i have to look at previous rows and create a new column. Please see below example and attached qvw.

If(Emp_ID=Previous(Emp_ID) and Bin <> Previous(Bin) and StartTime-Previous(StartTime) <= 2000, Previous(StartTime), StartTime) as NewColumn;

Here is example:

StartTime, Bin, Emp_ID

27011, SINGLES-BULK-082, A
27051, SINGLES-BULK-055, A

Output would be,

StartTime, Bin, Emp_ID, NewColumn

27011, SINGLES-BULK-082, A, 27011
27051, SINGLES-BULK-055, A, 27011

Any help would be highly appreciated.

Re: Compare Previous Values and create new field

markgraham123 — Fri, 24 May 2019 14:42:13 GMT

@sunny_talwar

Re: Compare Previous Values and create new field

sunny_talwar — Fri, 24 May 2019 14:48:56 GMT

Try this

T2: LOAD StartTime, Bin, Emp_ID, If(Emp_ID = Previous(Emp_ID) and Bin <> Previous(Bin) and StartTime-Previous(StartTime) <= 2000, Previous(StartTime), StartTime) as NewColumn Resident T1 Order by Emp_ID;

Re: Compare Previous Values and create new field

markgraham123 — Fri, 24 May 2019 15:00:23 GMT

Sunny,

It is only picking first row and changing the value.

I want the values to be changed for all the values of the particular 'Bin'.

Please see attached.

Re: Compare Previous Values and create new field

sunny_talwar — Fri, 24 May 2019 15:14:14 GMT

May be this

T2: LOAD RowNo() as RowNum, StartTime, Bin, Emp_ID, If(Emp_ID = Previous(Emp_ID), If(Bin = Previous(Bin), Peek('NewColumn'), If(StartTime-Previous(StartTime) <= 2000, Previous(StartTime), StartTime)), StartTime) as NewColumn Resident T1 Order by Emp_ID;

Re: Compare Previous Values and create new field

markgraham123 — Fri, 24 May 2019 15:32:47 GMT

Sunny,

This is perfect. Meets my requirement.

What if i want to improvize this so that, the logic only wants to group 2 timings and give previous time for 2nd one.

Once it reaches 3rd time, my logic should treat 3rd time as new timing and look for 4th timing and group it to 3rd timing.

Re: Compare Previous Values and create new field

sunny_talwar — Fri, 24 May 2019 15:45:19 GMT

You would have to elaborate on this with an example and it's output

Re: Compare Previous Values and create new field

markgraham123 — Fri, 24 May 2019 19:27:48 GMT

@sunny_talwar I apologize for the confusion...

Here is my sample data and expected output:

1. I sort it by Emp_ID and StartTime.

If(Emp_ID = Previous(Emp_ID),
If(Bin = Previous(Bin), Peek('NewColumn'),
If(StartTime-Previous(StartTime) <= 2000, Previous(StartTime), StartTime)), StartTime) as NewColumn

I can apply above logic only for 2 items for one timing. Once i reach 3rd item, i have to start fresh group with 3rd timing and run the same logic.

StartTime	Bin	Emp_ID	NewColumn
27011	SINGLES-BULK-082	A	27011
27948	SINGLES-BULK-085	A	27011 (Since i reached 2 items, i will start logic from next timing now)
29308	SINGLES-BULK-091	A	29308
29804	SINGLES-BULK-066	A	29308
31604	SINGLES-BULK-039	A	31604
35885	SINGLES-BULK-095	A	35885
36444	SINGLES-BULK-063	A	35885
37586	SINGLES-BULK-097	A	37586
38016	SINGLES-BULK-090	A	37586