topic Re: Generate missing data - daynumbering omitting weekends. in App Development

Generate missing data - daynumbering omitting weekends.

OysteinT — Tue, 08 Jun 2021 07:52:27 GMT

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...

Re: Generate missing data - daynumbering omitting weekends.

Taoufiq_Zarra — Tue, 08 Jun 2021 11:11:25 GMT

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:

Re: Generate missing data - daynumbering omitting weekends.

OysteinT — Tue, 08 Jun 2021 11:24:32 GMT

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

Re: Generate missing data - daynumbering omitting weekends.

Taoufiq_Zarra — Tue, 08 Jun 2021 11:30:45 GMT

@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:

Re: Generate missing data - daynumbering omitting weekends.

OysteinT — Tue, 08 Jun 2021 12:02:43 GMT

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.

Re: Generate missing data - daynumbering omitting weekends.

Taoufiq_Zarra — Tue, 08 Jun 2021 12:40:47 GMT

@OysteinT

dayCount not DayCount

Re: Generate missing data - daynumbering omitting weekends.

OysteinT — Tue, 08 Jun 2021 12:47:06 GMT

Thanks! 🙂

Now it looks good and I know how to proceed.