topic Re: Exclusion of dates in QlikView

Exclusion of dates

dineshkumar — Tue, 17 Jan 2017 10:19:28 GMT

hi all,

i have atached two files one is holiday list and other contains dimensions.

in my report i want a column resolution date which is walkin date+2 (excluding holiday in holiday list)

e.g. if today is friday, then friday(06/01/2017) +2 is actually sunday(08/01/2017) but i want tuesday(10/01/2017)(as saturday, sunday are holiday), in case if monday(09/01/2017) is a holiday i want resolution date as wednesday(11/01/2017)

thanks in advance

Re: Exclusion of dates

marcus_sommer — Tue, 17 Jan 2017 15:26:20 GMT

Try it with lastworkday() maybe in this way:

lastworkday(Date, 2, '$(=concat(Holidays, chr(39) & ',' & chr(39)))')

- Marcus

Re: Exclusion of dates

dineshkumar — Wed, 18 Jan 2017 11:45:23 GMT

Hi marcus,

Already tried, its not considering date in holiday list, by default it takes sat sunday nly as holiday and displaying result...

Im having:

set vHoliday=chr(39)&concat(Holidays_Dates, chr(39) & ',' & chr(39))&chr(39);

load

LastWorkDate(Date_column, 3, '$(=vHoliday)')

.from ... . . .

but if there is three, four consecutive holiday it fails.

Re: Exclusion of dates

marcus_sommer — Wed, 18 Jan 2017 12:15:37 GMT

My statement was for working within the gui and couldn't be applied directly within the script because without a lookup-function you couldn't access fields from other tables, concat() would require an aggregation load and within the script is only a one-step $-sign expansion possible like $(var) and not a two-step expansion like $(=var). Within the script you need something like this:

Holidays:

load chr(39) & concat(Holdidays, chr(39) & ',' & chr(39)) & chr(39) as HolidayList

from Holidays;

let vHolidayList = peek('HolidayList', 0, 'Holidays');

drop tables Holidays;

load

LastWorkDate(Date_column, 3, $(vHolidayList))

.from ... . . .

- Marcus

Re: Exclusion of dates

qliknirmal — Fri, 31 Mar 2023 11:58:41 GMT

Hi Dinesh,

Have you got the solution for this issue.