Generate missing data - daynumbering omitting weekends.

[OysteinT]

Hi I have two tables I am trying to link/marge - but in one of them I am lacking some data and am looking to generate it to fill in the gaps...

source table 1: Absences

Date_ID	Date	Reason	BadgeNO	Level
23.04.2021_111	23.04.2021	Sickness	111	1
24.04.2021_111	24.04.2021	Sickness	111	1
25.04.2021_111	25.04.2021	Sickness	111	1
26.04.2021_111	26.04.2021	Sickness	111	1
27.04.2021_111	27.04.2021	Sickness	111	1

source table 2: Schedules

Date_ID	ScheduleDateDate	ScheduleBadgeNO	ScheduleName	DayCount	Hours
23.04.2021_111	23.04.2021	111	Sickness	8	7
26.04.2021_111	26.04.2021	111	Sickness	11	7
27.04.2021_111	27.04.2021	111	Sickness	12	7

WHat I want to generate is the lines for the missing dates in table 2 - either in the load script for it - or in a visualization:

For example - a table visualization with:
Date - Reason - BadgeNo - DayCount - Sum(Hours)

The visualization now shows:

Day	Rason	BadgeNo	DayCount	Hours
23.04.2021	Sickness	111	8	7
24.04.2021	Sickenss	111	-	0
25.04.2021	Sickness	111	-	0
26.04.2021	Sickness	111	11	7
27.04.2021	Sickness	111	12	7

And I want to fill in the DayCount for the missing values... 

[Taoufiq_Zarra]

hi,

if I understood correctly just add left join

like :

load * ... from table1

left join load * .... from table2

Data:

LOAD * INLINE [
    Date_ID, Date, Reason, BadgeNO, Level
    23.04.2021_111, 23.04.2021, Sickness, 111, 1
    24.04.2021_111, 24.04.2021, Sickness, 111, 1
    25.04.2021_111, 25.04.2021, Sickness, 111, 1
    26.04.2021_111, 26.04.2021, Sickness, 111, 1
    27.04.2021_111, 27.04.2021, Sickness, 111, 1
];

left join

LOAD * INLINE [
    Date_ID, ScheduleDateDate, ScheduleBadgeNO, ScheduleName, DayCount, Hours
    23.04.2021_111, 23.04.2021, 111, Sickness, 8, 7
    26.04.2021_111, 26.04.2021, 111, Sickness, 11, 7
    27.04.2021_111, 27.04.2021, 111, Sickness, 12, 7
];

 output:

[OysteinT]

Hi

no, what I want to do is generate the missing DayCount transactions (9 and 10 in this case). They are not in table 2 (the source of the DayCount figure) as those two dates aren't there. 

Either that or somehow just make the field DayCount in the visualization generate the missing value on the fly. 

My desired result is (figures in red are the ones I am missing today):

Day	Rason	BadgeNo	DayCount	Hours
23.04.2021	Sickness	111	8	7
24.04.2021	Sickenss	111	9	0
25.04.2021	Sickness	111	10	0
26.04.2021	Sickness	111	11	7
27.04.2021	Sickness	111	12	7

[Taoufiq_Zarra · Accepted solution]

@OysteinT  ah ok

you can check :

Tmp:

LOAD * INLINE [
    Date_ID, Date, Reason, BadgeNO, Level
    23.04.2021_111, 23.04.2021, Sickness, 111, 1
    24.04.2021_111, 24.04.2021, Sickness, 111, 1
    25.04.2021_111, 25.04.2021, Sickness, 111, 1
    26.04.2021_111, 26.04.2021, Sickness, 111, 1
    27.04.2021_111, 27.04.2021, Sickness, 111, 1
];

left join

LOAD * INLINE [
    Date_ID, ScheduleDateDate, ScheduleBadgeNO, ScheduleName, DayCount, Hours
    23.04.2021_111, 23.04.2021, 111, Sickness, 8, 7
    26.04.2021_111, 26.04.2021, 111, Sickness, 11, 7
    27.04.2021_111, 27.04.2021, 111, Sickness, 12, 7
];


Data:
noconcatenate

load Date_ID, Date, Reason, BadgeNO, Level,ScheduleDateDate, ScheduleBadgeNO, ScheduleName, if(len(trim(DayCount))=0,peek(DayCount)+1,DayCount) as DayCount,if(len(trim(Hours))=0,0,Hours) as Hours ;
load * resident Tmp order by Date;

drop table Tmp;

output:

[OysteinT]

Hmm, your test script works fine - but my app doesn't when I apply the logic to it. 

I get my leaves first in the format of From - To date, which I then transform into a single date format. 
Attached the qvf file and the example sourcefile. 

[Taoufiq_Zarra]

@OysteinT 

dayCount not DayCount

[OysteinT]

Thanks! 🙂 

Now it looks good and I know how to proceed.