Identify Shifts for hour entries / RangeSum

[wmarkett1983]

Hello All,

i am looking for a solution to identify the shifts per employee for their hour entries, but I have no specific time range or other flag to identifiy the hours entries. There just the following 2 specifications to identify the night shift:

- hour entry will be seperated at midnight

- all hours entries with no break or a break <= 120 minutes before and after the midnight are part of the night shift.

Example Data:

Employee	Date Start	Date Ende	Minutes to Next Our Entry	Shift Start	Sequenz (Target)
Hello Test	1.10.18 6:00 PM	1.10.18 9:33 PM	4	1	1
Hello Test	1.10.18 9:37 PM	1.10.18 12:00 AM	0	0	1
Hello Test	1.10.18 12:00 AM	2.10.18 2:00 AM	480	0	1
Hello Test	2.10.18 10:00 AM	2.10.18 11:00 AM	420	1	2
Hello Test	2.10.18 6:00 PM	2.10.18 12:00 AM	0	1	3
Hello Test	2.10.18 12:00 AM	3.10.18 2:00 AM	0	0	3

My goal is to create the "Sequenz (Target)" Column in the load script, which represents the sum of all previous shift starts. I tried to solve it with Range Sum & above (only chart function) or previous (not possible to sum all previous rows). So my approach is to find a similar function to RangeSum & above als script function or a completly different approach, if you have one?

Thanks in advance for your support

Best Regards

Wilhelm

[dplr-rn]

Not sure if you tried it How about something like below in script

If(your_condition_tomatch_employee, previous([Sequenz (Target)])+[ShiftStart],[ShiftStart])

seems to cover the scenario mentioned above.

hope it helps

[wmarkett1983]

Thanks for your reply. The challenge is that the column [Sequenz (Target)] does not exist and it is the one i have to create.

Or is it possible to use with the previous function directly the new created value from the row before?

per Example:

left join load ID, If(ConditionIfRowIsFirstLine,1,previous([Sequenz (Target)])+1) as [Sequenz (Target)] resident testtable;

[dplr-rn]

Never tried it like that. but should be possible

[wmarkett1983]

Error Message: [Sequenz (Target)] not found

[wmarkett1983 · Accepted solution]

tmp: Load distinct RowNo() as ShiftID, RowID, RowID+1 as RowIDStart, 1 as X Resident HourTable where ShiftStart=1 order by RowID;

left join load 1 as X, max(RowID) as MAX_ROWID Resident HourTable group by 1;

left join load RowID, if(isnull(Previous(RowID)-1) or Previous(RowID)-1<RowID,MAX_ROWID,Previous(RowID)-1) as RowIDEnd Resident tmp order by RowID desc;

max: load X, max(RowIDEnd-RowIDStart) as Anzahl Resident tmp group by X;

LET vCounter = ceil(Peek('Anzahl'));

DROP TABLE max;

FOR i=1 to $(vCounter)

Concatenate (tmp)

Load RowID +$(i) as RowID,

ShiftID

Resident tmp

where RowID +$(i) <= RowIDEnd;

NEXT i;

left join (HourTable) load RowID, ShiftID Resident tmp;

DROP TABLE tmp;