Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Count holidays on each employee

Hi Guys,

I have 3 tables:

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

NumberHolidays
126
215
321

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

    

NumberDate fromDate to
12017-01-012017-01-02
12017-01-032017-01-03
12017-01-042017-01-05
22017-01-142017-01-17
22017-01-172017-01-18
32017-01-052017-01-07
32017-01-082017-01-15

And i want to get result:

    

NumberDate fromDate toHow many days for each employee has left?
12017-01-012017-01-0224
12017-01-032017-01-0323
12017-01-042017-01-0521
22017-01-142017-01-1711
22017-01-172017-01-189
32017-01-052017-01-0718
32017-01-082017-01-1510

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

1 Solution

Accepted Solutions
MarcoWedel

Hi,

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

QlikCommunity_Thread_246644_Pic1.JPG

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

View solution in original post

6 Replies
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

Hi,

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

QlikCommunity_Thread_246644_Pic1.JPG

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
Not applicable
Author

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
Not applicable
Author

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
Not applicable
Author

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