Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!!!
can u share sample data
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
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;
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 |
if(Type = 'holiday', 'Holiday', 'Normal') as Indicator
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
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