Compare Previous Values and create new field

[markgraham123]

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.

[markgraham123]

@sunny_talwar 

[sunny_talwar]

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;

[markgraham123]

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.

[sunny_talwar]

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;

[markgraham123]

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.

[sunny_talwar]

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

[markgraham123]

@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