Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
CUSTOMERS ONLY: Now accepting customer applications for the 2023 Luminary Program: SUBMIT NOW
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
Peter_Cammaert
Partner - Champion III
Partner - Champion III

$-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
Partner - Creator III
Partner - Creator III

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