Problems with HOLIDAYS in Date functions

[Anonymous]

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

[marcus_sommer]

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]

try using mapping tables and apply maps.

[Anonymous]

Thanks for the advice!

Sadly, it didn't solve my problem.

[Anonymous]

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]

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

[Anonymous]

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.

[Anonymous]

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]

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