Create new rows during load

alenb — Thu, 17 Feb 2022 16:31:00 GMT

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

Re: Create new rows during load

MarcoWedel — Thu, 17 Feb 2022 17:29:31 GMT

what if your Hours are not divisible by 8?

Re: Create new rows during load

alenb — Fri, 18 Feb 2022 15:29:05 GMT

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

Re: Create new rows during load

alenb — Fri, 18 Feb 2022 17:10:37 GMT

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;

topic Re: Create new rows during load in App Development

Create new rows during load

Re: Create new rows during load

Re: Create new rows during load

Re: Create new rows during load