Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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...
@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:
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:
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 |
@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:
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.
dayCount not DayCount
Thanks! 🙂
Now it looks good and I know how to proceed.