Loading Holidays in script (Load inline, then concat), can only add one holiday

[JacobJones]

Hi All,

I'm trying to load my holidays in my load script as follows:

Holidays:
LOAD * INLINE [
Holidays
'2019-01-01'
'2019-01-02'
];
Load
concat(Holidays, ',') as ALL_HOLIDAYS Resident Holidays;
drop table Holidays;

If there is only one date, it works, but as soon as I add the other dates, it stops working.

What is going wrong here?

[StarinieriG]

Hi,

I tried to load your script but it seems to work fine.

What is your expected result?

[JacobJones]

In my example there are two dates, when I load that script it doesn't work. The holidays are not subtracted from the networkdays. If I use the same script with one only day, it does work.

[StarinieriG]

So, try to add chr(39) before and after the field

concat(chr(39) & Holidays & chr(39), ',') as ALL_HOLIDAYS

[JacobJones]

So now I've tried this:

Holidays:
LOAD * INLINE [
Holidays
'2019-01-01'
'2019-01-02'
];
Load
concat(chr(39) & Holidays & chr(39), ',') as ALL_HOLIDAYS Resident Holidays;
drop table Holidays;

As well as replacing the comma after the last chr(39) with an &, but it still doesn't work. Infact doing it this doesn't work for even just one date. 

Thanks for trying to help, this is driving me bonkers

[StarinieriG · Accepted solution]

How are you using then this field to subtract holidays from networkdays?

Normally I do something like this:

1) Create the field like concat(chr(39) & Holidays & chr(39), ',') as ALL_HOLIDAYS

LET Holiday = Peek('ALL_HOLIDAYS');

2) Use that variable inside networkdays(start,end,$(Holiday))

[DavidM]

What is your goal here? How exactly are you trying to subtract your holidays from net working days?

Because right now, you would get field: 2019-01-01,2019-01-02

How exactly do you want to subtract that?

How about when you have already holiday table, you can join/map it to your calendar and create HolidayFlag which you can use in Set analysis

[JacobJones]

I'm trying to use it like this:

=count(If(IsNull([Date of Appointment]) AND NetWorkDays([Date Case Received], Today(), ALL_HOLIDAYS) <= 8, [PAT_ID]))

I understand there are other ways, and I am interested in them, however, I also want to understand load inline and concat and why this isn't working so I have a better understanding of Qlik overall. 

[JacobJones · Accepted solution]

Thank you so much for guiding me through that. In the end it looks like I wasn't using the variable correctly. I was writing 

NetWorkDays([Date Case Received], Today(), Holiday) - 8 & ' days'

instead of 

NetWorkDays([Date Case Received], Today(), $(Holiday)) - 8 & ' days'

I will go over variables, to get a better understanding of what the difference is. Thanks again.