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

[Peter_Cammaert]

$-Sign expansion is a weird beast. It is actually some sort of text pre-processor that is reading & translating text streams even before the interpreter tries to lift a series of tokens from the input to assemble the next statement.

Now this so-called pre-processor does its job before the statement is assembled, interpreted and executed. That doesn't mean that $-sign substitution cannot happen multiple times in a row (it can, when for example you start nesting $-sign substitutions) but those substitutions will happen all before the actual statement is read. And not while the statement is read or interpreted or executed. Once a LOAD has been parsed to completion, it will not change anymore during execution.

So your attempt to change a single LOAD statement everytime a different ISO_CODE is found in the input stream won't work... The $-sign substitution will be handled the first time the LOAD statement is encountered (and will probably produce the wrong value because no data has been read yet) and never again while that same LOAD statement is running.

[maneshkhottcpl]

Hi Jose

Do you find any solution for your problem?

let me know if you have solution so that i can use it

Regard

Manesh