Count holidays on each employee

[Anonymous]

Hi Guys,

I have 3 tables:

Table 1 (Number is  a number of employee and how many days of holiday employee has)

Number	Holidays
1	26
2	15
3	21

Table 2 (all days within dates each employee have already took)

Number	Date from	Date to
1	2017-01-01	2017-01-02
1	2017-01-03	2017-01-03
1	2017-01-04	2017-01-05
2	2017-01-14	2017-01-17
2	2017-01-17	2017-01-18
3	2017-01-05	2017-01-07
3	2017-01-08	2017-01-15

And i want to get result:

Number	Date from	Date to	How many days for each employee has left?
1	2017-01-01	2017-01-02	24
1	2017-01-03	2017-01-03	23
1	2017-01-04	2017-01-05	21
2	2017-01-14	2017-01-17	11
2	2017-01-17	2017-01-18	9
3	2017-01-05	2017-01-07	18
3	2017-01-08	2017-01-15	10

So the last table is telling how many days have left for each range of dates.

So employee Number 1 got free days from 2017-01-01 to 2017-01-02 ( 2 days) so 26 - 2 = 24.

But When you are looking at his next holidays from 2017-01-03 to 2017-01-03 (1 day) I have to take 24(from above row)

- 1 free day = 23.

Please help me write the best solution script.

Best Regards,

Jacek Antek

[marcus_sommer]

I think you could try something like this:

Map_HolidaysTotal:

mapping load Number, Holidays from Holidays;

HolidaysTakenAndLeft:

load *, HolidaysTotal - HolidaysTakenTotal as HolidaysLeft;

load

     Number, [Date from], [Date to],

     applymap('Map_HolidaysTotal', Number, 0) as HolidaysTotal,

     rangesum(-[Date from], [Date to], 1) as HolidaysTaken,

     if(Number = peek('Number'),

          rangesum(peek('HolidaysTakenTotal'),

          rangesum(-[Date from], [Date to], 1)) as HolidaysTakenTotal

resident HolidaysTaken order by Number, [Date from];

- Marcus

[MarcoWedel · Accepted solution]

Hi,

another quite similar solution (restricted to already sorted input tables without null values though) might be:

table1:
LOAD * FROM [https://community.qlik.com/thread/246644] (html, codepage is 1252, embedded labels, table is @1);

mapHoliday:
Mapping LOAD * Resident table1;

table2:
LOAD *,
    If(Previous(Number)=Number,Peek(HolidaysLeft),ApplyMap('mapHoliday',Number))+[Date from]-[Date to]-1 as HolidaysLeft
FROM [https://community.qlik.com/thread/246644] (html, codepage is 1252, embedded labels, table is @2);

hope this helps

regards

Marco

[Anonymous]

Thank you Guys for your help!

The better solution for me is Marco solution. But Marcus I also appreciate.

Thank you once more time!

Best Regards,

Jacek Antek

[Anonymous]

HolidaysTakenAndLeft:

load *, HolidaysTotal - HolidaysTakenTotal as HolidaysLeft;

load

     Number, [Date from], [Date to],

     applymap('Map_HolidaysTotal', Number, 0) as HolidaysTotal,

     rangesum(-[Date from], [Date to], 1) as HolidaysTaken,

     if(Number = peek('Number'),

          rangesum(peek('HolidaysTakenTotal'),

          rangesum(-[Date from], [Date to], 1)) as HolidaysTakenTotal

resident HolidaysTaken order by Number, [Date from];

This is very interesting construction.

How should I read this?

Jacek

[Anonymous]

It will be one table3.

first of all do code below and after that load all of this and add this part?:

load *, HolidaysTotal - HolidaysTakenTotal as HolidaysLeft;

[marcus_sommer]

To put load on load is called Preceding Load and quite useful to adjust loadings from databases but also to keep a clear load-chain without the need to do everything with resident-loads (if load-performance is critical you would need a testing on the various load-approaches and it would rather tend against the preceeding load).

The resident-load with order by clause marked it bold is necessary to ensure that your records comes in the right order for using interrecord-functions like previous() or peek().

- Marcus