Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER 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

Something like this?

// The Facts table determines which ISO Codes should produce Holiday lists

ListOfISOCodes:

LOAD Concat(DISTINCT chr(39) & ISO_CODE & chr(39), ', ') AS ListOfISOCodes RESIDENT FACT_ORDER_DETAIL_VIEW;

LET vISOCodes = Peek('ListOfISOCodes');

DROP Table ListOfISOCodes;

FOR Each vISOCode in $(vISOCodes)

// Will create a list only if at least one holiday can be found !

  ListOfHolidays:

  LOAD Concat(DISTINCT ', ' & Chr(39) & BANK_HOLIDAY & chr(39), '') AS ISOHolidays

  RESIDENT BANK_HOLIDAY

  WHERE ISO_CODE = '$(vISOCode)';

  LET vISOHolidays = Peek('ISOHolidays');

  DROP Table ListOfHolidays;

// Nevermind the missing comma. We may encounter ISO Codes without Holiday lists...

  NewFactsTable:

  LOAD PRIMARY_KEY, START_DATE, ISO_CODE,

       Date(LastWorkDate(START_DATE, 4 $(vISOHolidays))) AS EXPECTED_ARRIVAL_DATE

  RESIDENT FACT_ORDER_DETAIL_VIEW

  WHERE ISO_CODE = '$(vISOCode)';

  LET vISOHolidays =;

NEXT vISOCode

It looks a bit rough but it seems to work allright. The script also takes into account that some ISO_CODE values may lack a list of holidays in table BANK_HOLIDAY. In that case LastWorkDate() doesn't get a faulty parameter list

arvind1494
Specialist
Specialist

use mapping load instead of lookup

jdolzfer
Contributor III
Contributor III
Author

Thanks for the answer, but I don't see it as a good solution for my purpose.

I inherited this code and they use a parcial solution by counting the total holidays between two dates that every row has, but that's not the approach I want. I need to use the holidays as the third argument of the Date functions.

Using lookups or maps does not work.

jdolzfer
Contributor III
Contributor III
Author

This code should do the work but, as you mention, it's a bit rough.

The inherited code uses lastworkdate and networkdates a lot of time... It will be difficult to convince the managers and others developers to substitute a pair lines of code for 29...

Peter_Cammaert
Partner - Champion III
Partner - Champion III

I'm not going to comment on code and/or issues that I haven't had the chance to review.

Just keep in mind that the FOR loop and all preparations in the above example consitute a framework to which you can add all other required data manipulations. Meaning that inserting another LOAD with calls to networkdays(), lastworkdate() and/or firstworkdate() will probably increase the number of lines from 29 to 34 or something.

I'm not sure that you can introduce dynamic holiday list processing with just another 2 lines of script code.

jdolzfer
Contributor III
Contributor III
Author

Hi Peter,

Thanks again for your answer.

The FOR loop you pasted inspirated me to create dynamic variables for each country. I've made this:

//-----------------------  Dynamic creation of Holidays variables by Country --------------------

ISO_CODES_TEMP:

LOAD Concat(DISTINCT chr(39) & ISO_CODE & chr(39), ', ') AS ListOfISOCodes

RESIDENT FACT_ORDER_DETAIL_VIEW;

LET vISOCodes = PEEK('ListOfISOCodes');

DROP TABLE ISO_CODES_TEMP;

FOR Each vISOCode in $(vISOCodes)

  

    country_holidays_tmp:

    LOAD COUNTRY_HOLIDAYS AS CTR_HD_TMP

    RESIDENT COUNTRY_HOLIDAYS_LIST

    WHERE ISO_CODE = '$(vISOCode)';

  

    // Create the name of the variable dynamically

    LET vName = 'vHolidays_' & '$(vISOCode)';

    LET '$(vName)' = PEEK('CTR_HD_TMP');

  

    DROP Table country_holidays_tmp;

  

    LET vName=;

NEXT vISOCode

And it works.

The only big problem  I have now it's to call the variables dynamically. The next code does not work:

LOAD PRIMARY_KEY,

    $(=('vHolidays_' & ISO_CODE)) AS HOLIDAYS_TEST,

RESIDENT FACT_ORDER_DETAIL_VIEW

marcus_sommer
MVP & Luminary
MVP & Luminary

Try it in this way:

LOAD PRIMARY_KEY,

   '$(vHolidays_)' & ISO_CODE AS HOLIDAYS_TEST

RESIDENT FACT_ORDER_DETAIL_VIEW;

- Marcus

jdolzfer
Contributor III
Contributor III
Author

That does not work.

In your code, you're calling a variable called vHolidays_ and, after getting its value, you concatenate the value of the field ISO_CODE.

marcus_sommer
MVP & Luminary
MVP & Luminary

I did not look deeply within your above loop which worked like you said - I just corrected the syntax to combine the variable with the field to create another one.

- Marcus

Peter_Cammaert
Partner - Champion III
Partner - Champion III

Sorry for repeating myself, but you really should read this line from my earlier post

$-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.