topic Re: holidays in QlikView

holidays

Fri, 20 Nov 2020 18:02:30 GMT

In Qlikview i need the option to select with 2 values "Holiday" and "non-holiday". This way i will have all the days (holidays and non-holidays) at the same time. When i select one of the selections i will only get those days.

Can somebody help me to create this?

I do have an Excel list with all the holidays from the last few years but now i have to match them with the date field i'm using in Qlikview.

I hope somebody can help me with this!!!

Re: holidays

Tue, 11 Nov 2014 14:05:10 GMT

can u share sample data

Re: holidays

its_anandrjs — Tue, 11 Nov 2014 14:08:26 GMT

Hi,

Yes this is possible as you say you have excel file if there is any flag field it is identified as the holiday or non holiday can you provide any sample for this.

Regards

Anand

Re: holidays

Gysbert_Wassenaar — Tue, 11 Nov 2014 14:10:01 GMT

Maybe like this:

Temp:

LOAD Date, ...other fields...

FROM ...source...;

join(Temp)

LOAD Date, 1 as Flag

FROM ...excel_file...;

Result:

LOAD *, if(Flag, 'Holiday', 'non-holiday') as Indicator

RESIDENT Temp;

DROP TABLE Temp;

Re: holidays

Tue, 11 Nov 2014 15:07:57 GMT

i have an excel file like this (but then in Dutch):

Date	Holiday	Type
25-12-2013	Christmas	holiday
26-12-2013	Christmas	holiday
31-12-2013	New Year's Eve	holiday
01-01-2014	New Year	holiday

Re: holidays

dmac1971 — Tue, 11 Nov 2014 15:17:25 GMT

if(Type = 'holiday', 'Holiday', 'Normal') as Indicator

Re: holidays

MarcoWedel — Wed, 12 Nov 2014 12:07:23 GMT

Hi,

another solution could be:

tabHolidayCalendar:  //LOAD from Excel in your case
LOAD * INLINE [
    Date, Holiday, Type
    25-12-2013, Christmas, holiday
    26-12-2013, Christmas, holiday
    31-12-2013, "New Year's Eve", holiday
    01-01-2014, New Year, holiday
];

LOAD *
Where not Exists(Date);
LOAD Date('20-12-2013'+RecNo()-1) as Date,
    Null() as Holiday,
    'non-holiday' as Type
AutoGenerate '05-01-2014'-'20-12-2013'+1;

hope this helps

regards

Marco

Re: holidays

its_anandrjs — Wed, 12 Nov 2014 13:08:46 GMT

Hi,

Then you can try this ways

Transac:

LOAD

Date('01/01/2012' + RowNo() - 1) as Date

AutoGenerate

Date('12/13/2014') - Date('01/01/2012') + 1;

Join

ExcelSource:

LOAD Date, Holiday,Type, 1 as HolidayFlag;

LOAD Alt(Date#(Date,'DD-MM-YYYY'), Date(Date,'M/D/YYYY')) AS Date,

Holiday,

Type

FROM

[holidaylist.xlsx]

(ooxml, embedded labels, table is Sheet1);

Final:

LOAD

Date, Holiday AS HolidayDesc,

if(HolidayFlag,'Holiday', 'non-holiday') as HolidayIndicator1,

if(Type = 'holiday','Holiday', 'non-holiday') as HolidayIndicator2

Resident Transac;

DROP Table Transac;

Regards

Anand