Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
jdolzfer
Contributor III
Contributor III

Problems with HOLIDAYS in Date functions

Hi,

I'm developing a Qlikview dashboard and I have a table where I load the bank holidays by country:

COUNTRY_HOLIDAYS_LIST:
LOAD DISTINCT ISO_CODE,
CONCAT(DISTINCT Chr(39) & BANK_HOLIDAY & chr(39), ',') AS COUNTRY_HOLIDAYS
RESIDENT BANK_HOLIDAY
GROUP BY ISO_CODE;

What I'm trying is to use the country holiday list in date function like LastWorkDate. I tried to create a $function but it's not working. My last try is using the lookup function:


LOAD PRIMARY_KEY,
    DATE(START_DATE, 4, Lookup('COUNTRY_HOLIDAYS', 'ISO_CODE', ISO_CODE, 'COUNTRY_HOLIDAYS_LIST'))) AS EXPECTED_ARRIVAL_DATE,
    Lookup('COUNTRY_HOLIDAYS', 'ISO_CODE', ISO_CODE, 'COUNTRY_HOLIDAYS_LIST') AS CTRY_BH_TEST
RESIDENT FACT_ORDER_DETAIL_VIEW;


As you can see, I put the same lookup function in a test field called CTRY_BH_TEST. When I reload the script, this field gets the Bank Holidays list by country correctly:

'01/01/2018','01/05/2018', etc.

But, if I look to the EXPECTED_ARRIVAL_DATE, I found that some expeted dates are 01/05/2018.

I've been working a whole week to resolve this and I'm going mad. Anyone can help?

Thanks in advance

21 Replies
marcus_sommer
MVP & Luminary
MVP & Luminary

I'm not sure if the lookup() will be treated as a valid holiday-listing within the lastworkdate() or if it is just ignored. Therefore try it with a evaluate-wrapping like: evaluate(lookup(...))

- Marcus

bramkn
Partner
Partner

try using mapping tables and apply maps.

jdolzfer
Contributor III
Contributor III
Author

Thanks for the advice!

Sadly, it didn't solve my problem.

jdolzfer
Contributor III
Contributor III
Author

Hi Bram,

thanks for the advice!

Unfortunately, it didn't work.

I made this mapping table:



COUNTRY_HOLIDAYS_LIST:
mapping LOAD DISTINCT ISO_CODE,
    CONCAT(DISTINCT Chr(39) & BANK_HOLIDAY & chr(39), ',') AS COUNTRY_HOLIDAYS
RESIDENT BANK_HOLIDAY
GROUP BY ISO_CODE;

And then:


LOAD PRIMARY_KEY,
    DATE(LastWorkDate(START_DATE, 4, 
            ApplyMap('COUNTRY_HOLIDAYS_LIST', ISO_CODE, NULL())
        )
    ) AS EXPECTED_ARRIVAL_DATE,
    ApplyMap('COUNTRY_HOLIDAYS_LIST', ISO_CODE, NULL()) AS CTRY_BH_TEST
RESIDENT FACT_ORDER_DETAIL_VIEW;

and I obtained the same result

bramkn
Partner
Partner

concat with chr(39)?? Remove those to start.

jdolzfer
Contributor III
Contributor III
Author

That doesn' work. You need to put the dates between apostrophes.

If you put the next expression in a text box (format DD/MM/YYYY):

=LastWorkDate('27/04/2018', 3, 01/05/2018,10/05/2018,21/05/2018)

it will show you the date 01/05/2018 instead of 02/05/2018.

Peter_Cammaert

I guess your problem lies in the fact that LastWorkDate() expects a variable list of parameters, not a variable containing a variable list of parameters. AFAIK you cannot do it that way. Patching a statement with the return value from a function call in that same statement will almost certainly cause problems with statement optimizers and precompilers.

$-sign expansion won't help either, because the expansion will be performed only once when the LOAD statement is parsed, and not row-by-row.

Maybe you should try with a FOR loop and a separate CONCATENATE LOAD for each individual ISO CODE value. That code would not look very smart, but at least it will work.

jdolzfer
Contributor III
Contributor III
Author

Thanks for the answer, Peter!

What kind of FOOR loop do you have in mind ?

I've tried to put one country bank holidays into a variable, like GERMANY_HOLIDAYS, and when using:

LastWorkDate(start_date, 3, $(GERMANY_HOLIDAYS))

It worked quite well.

The problem in here is putting a conditional (IF/SWITCH) has holidays expression... because we use lastworkdate, firstworkdate, networkdates, etc... so many times.

marcus_sommer
MVP & Luminary
MVP & Luminary

In general a variable will always work if their content if they is called looked like as had you written it manually. In your case it's not a valid list of dates else it are calculations like 1 / 5 / 2018 = 0.0xxxxxx. Instead of this your variable-content should be look like:

'01/05/2018','10/05/2018','21/05/2018'

This meant you need to add single-quotes to each date during the concat() or loop-generation.

Beside this I personally would probably use a different approach by flagging working- and holidays within a master-calendar. I use this approach since many years and it worked very well whereby I have only one holiday-list. In your case are multiple ISO listings necessary and therefore I think I would create for it a kind of special calendar (beside a normal master-calendar) in which each ISO had itsown records (just concatenating them) and linking tis table per combined key of ISO and date to your other tables. Here an examples to what is meant: Master Calendar with movable holidays.

- Marcus