Create new rows during load

[alenb]

I would need to convert the first table into a second. So for every increment of 8 Hours, another date needs to be added. 

[MarcoWedel]

what if your Hours are not divisible by 8?

[alenb]

It will always be  0, 4 or a multiple of 8. 

[alenb · Accepted solution]

I did manage to solve it. Here's a solution with slightly different field names.

TIMEOFF_TEMP:
Load * Inline [
Date, timeoff
2021-01-01, 4
2021-01-02, 24
2021-01-08, 8
];

MinMaxDate:
Load Min(Date) as MinDate, Max(Date) as MaxDate resident TIMEOFF_TEMP;

Let vMinDate = Peek('MinDate',-1,'MinMaxDate') - 1;
Let vMaxDate = Peek('MaxDate',-1,'MinMaxDate') ;

Drop Table MinMaxDate;

Join (TIMEOFF_TEMP)
Load Date(recno()+$(vMinDate)) as Date Autogenerate vMaxDate - vMinDate;

TIMEOFF_TEMP2:
NoConcatenate Load
Date,
if(IsNull(timeoff) and peek(timeoff) > 8,
peek(timeoff) - 8,
timeoff
) as timeoff
Resident TIMEOFF_TEMP order by Date;

Drop Table TIMEOFF_TEMP;

TIMEOFF:
NoConcatenate Load
Date,
if(timeoff > 8, 8, timeoff) as timeoff

Resident TIMEOFF_TEMP2;

Drop Table TIMEOFF_TEMP2;