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]

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]

use mapping load instead of lookup

[Anonymous]

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.

[Anonymous]

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]

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.

[Anonymous]

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]

Try it in this way:

LOAD PRIMARY_KEY,

   '$(vHolidays_)' & ISO_CODE AS HOLIDAYS_TEST

RESIDENT FACT_ORDER_DETAIL_VIEW;

- Marcus

[Anonymous]

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]

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]

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.