topic Re: Problems with HOLIDAYS in Date functions in QlikView

Problems with HOLIDAYS in Date functions

Anonymous — Wed, 06 Jun 2018 12:17:20 GMT

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

Re: Problems with HOLIDAYS in Date functions

marcus_sommer — Wed, 06 Jun 2018 14:48:34 GMT

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

Re: Problems with HOLIDAYS in Date functions

bramkn — Wed, 06 Jun 2018 14:54:01 GMT

try using mapping tables and apply maps.

Re: Problems with HOLIDAYS in Date functions

Anonymous — Thu, 07 Jun 2018 07:37:38 GMT

Thanks for the advice!

Sadly, it didn't solve my problem.

Re: Problems with HOLIDAYS in Date functions

Anonymous — Thu, 07 Jun 2018 09:17:08 GMT

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

Re: Problems with HOLIDAYS in Date functions

bramkn — Fri, 08 Jun 2018 06:04:17 GMT

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

Re: Problems with HOLIDAYS in Date functions

Anonymous — Fri, 08 Jun 2018 07:01:55 GMT

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.

Re: Problems with HOLIDAYS in Date functions

Peter_Cammaert — Fri, 08 Jun 2018 11:25:59 GMT

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.

Re: Problems with HOLIDAYS in Date functions

Anonymous — Fri, 08 Jun 2018 14:37:02 GMT

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.

Re: Problems with HOLIDAYS in Date functions

marcus_sommer — Sat, 09 Jun 2018 08:37:00 GMT

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

Re: Problems with HOLIDAYS in Date functions

Peter_Cammaert — Sat, 09 Jun 2018 10:47:13 GMT

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

Re: Problems with HOLIDAYS in Date functions

arvind1494 — Sat, 09 Jun 2018 11:40:45 GMT

use mapping load instead of lookup

Re: Problems with HOLIDAYS in Date functions

Anonymous — Mon, 11 Jun 2018 14:46:06 GMT

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.

Re: Problems with HOLIDAYS in Date functions

Anonymous — Mon, 11 Jun 2018 14:49:55 GMT

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

Re: Problems with HOLIDAYS in Date functions

Peter_Cammaert — Tue, 12 Jun 2018 09:09:55 GMT

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.

Re: Problems with HOLIDAYS in Date functions

Anonymous — Tue, 12 Jun 2018 13:22:08 GMT

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

Re: Problems with HOLIDAYS in Date functions

marcus_sommer — Wed, 13 Jun 2018 04:53:41 GMT

Try it in this way:

LOAD PRIMARY_KEY,

'$(vHolidays_)' & ISO_CODE AS HOLIDAYS_TEST

RESIDENT FACT_ORDER_DETAIL_VIEW;

- Marcus

Re: Problems with HOLIDAYS in Date functions

Anonymous — Wed, 13 Jun 2018 08:29:45 GMT

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.

Re: Problems with HOLIDAYS in Date functions

marcus_sommer — Wed, 13 Jun 2018 08:41:18 GMT

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

Re: Problems with HOLIDAYS in Date functions

Peter_Cammaert — Wed, 13 Jun 2018 22:45:02 GMT

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.