Announcements
cancel
Showing results for
Did you mean:
Contributor II

## Identify Shifts for hour entries / RangeSum

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:

EmployeeDate StartDate EndeMinutes to Next Our EntryShift StartSequenz (Target)
Hello Test1.10.18 6:00 PM1.10.18 9:33 PM411
Hello Test1.10.18 9:37 PM1.10.18 12:00 AM001
Hello Test1.10.18 12:00 AM2.10.18 2:00 AM48001
Hello Test2.10.18 10:00 AM2.10.18 11:00 AM42012
Hello Test2.10.18 6:00 PM2.10.18 12:00 AM013
Hello Test2.10.18 12:00 AM3.10.18 2:00 AM003

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?

Best Regards

Wilhelm

1 Solution

Accepted Solutions
Contributor II
Author

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)

ShiftID

Resident tmp

where RowID +\$(i) <= RowIDEnd;

NEXT i;

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

DROP TABLE tmp;

5 Replies
Partner - Master III

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

Contributor II
Author

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;

Partner - Master III

Never tried it like that. but should be possible

Contributor II
Author

Contributor II
Author

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)

ShiftID

Resident tmp

where RowID +\$(i) <= RowIDEnd;

NEXT i;

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

DROP TABLE tmp;

Community Browser