topic Re: Date format in master calender in QlikView

Date format in master calender

ashis — Wed, 25 Nov 2020 16:16:04 GMT

In my transaction table my date field is like the following format

DATE#(Date(StartTimeReport,'DD-MMM-YY'),'DD-MMM-YY') AS Date

Now i have a master calender where i am using this date field for linking purpose.

I have one external excel file which has information about holidays and in this file my date is in
the following format DD/MM/YYYY eg 6/14/2017 .

Now i am doing a left join with the master calender temp and since the excel file has different date format
I am getting mixture of date formate in the Date field (DD-MMM-YY as well MM/DD/YYYY). I was trying to overcome the issue like the below code
But getting error Date1 field not found for left join.
Could you please help me how to overcome the issue so that i would get the Date in 'DD-MMM-YY' format;

For most of the weekend (Saturday and Sunday) I am getting inconsistent format

This is my complete code :

LET vWeeklyHolidays = 'Sat;Sun';

CalendarMaster_Temp:
LOAD
Date(Date) AS Date,
Year(Date) AS Year,
'Q' & Ceil(Month(Date) / 3) AS Quarter,
Date(MonthStart(Date),'MMM') as Month,
Day(Date) As Day,
WeekDay(Date) AS WeekDay,
Week(Date) As Week;
Load
Date(MinDate + IterNo() -1 ) AS Date
While (MinDate + IterNo() - 1) <= Num(MaxDate);
Load
Min(Date) AS MinDate,
Max(Date) AS MaxDate
RESIDENT Performance1;

LEFT JOIN(CalendarMaster_Temp)
LOAD Date(Date#([holidays],'MM/DD/YYYY'),'DD-MMM-YY') as Date,
1 AS IsHolidayFlag
FROM
[..\Holiday.xlsx]
(ooxml, embedded labels, table is Sheet1);

Calendar:
LOAD
Date,
Year,
Quarter,
Month,
Day,
WeekDay,
Week,
If(SubStringCount('$(vWeeklyHolidays)', WeekDay(Date)) OR IsHolidayFlag = 1, 1, 0) AS IsHolidayFlag,
If(SubStringCount('$(vWeeklyHolidays)', WeekDay(Date)) OR IsHolidayFlag = 1,'Non Working/Holiday', 'Working Day') AS DayType
Resident CalendarMaster_Temp;

DROP TABLE CalendarMaster_Temp;

Thank you,

Ashis

Re: Date format in master calender

jonathandienst — Tue, 29 Aug 2017 07:50:36 GMT

I think you are mixing up the date functions a little.

Date() is a date formatting function. It uses the underlying numeric and renders the date in the desired format. It does not change the underlying numeric value.

Date#() is a date interpretation (conversion) function. It takes a text value and converts it to the numeric date value using the format string supplied. It returns a date formatted in the same version.

Date(Date#(...., 'format1'), 'format2') is a common pattern to interpret a text date value in format1 and display it using format2.

In your case, if [holidays] is being correctly loaded as a date, the Date([holidays]) should be sufficient. If not, then [holidays] is coming in as a text string, and you would need

Date(Date#(holidays, 'MM/DD/YYYY')) as Date,

to interpret the date and render it in the default date format.

Re: Date format in master calender

its_anandrjs — Tue, 29 Aug 2017 07:54:50 GMT

Try with this in Left join table you select wrong field name please rectify that also what errors you get did you get MIN and MAX dates in resident load of the table1.

LEFT JOIN(CalendarMaster_Temp)
LOAD Date([holidays]) as Date1,
DATE(Date#([holidays]),'MM/DD/YYYY'),'DD-MMM-YY') AS Date
FROM
[..\Holiday.xlsx]
(ooxml, embedded labels, table is Sheet1);

Re: Date format in master calender

ashis — Tue, 29 Aug 2017 08:03:22 GMT

Thank you for your reply,I have tried that too, but similar result I am getting.

Re: Date format in master calender

ashis — Tue, 29 Aug 2017 08:05:31 GMT

Thank you for your reply, You are absolutely correct that I should use

Date(Date#(holidays, 'MM/DD/YYYY')) as Date, instead of

DATE#(Date(Date1,'MM/DD/YYYY'),'DD-MMM-YY') AS Date

Theoretically that should be the correct way , however If I do change it I get black dates . This is very awkward.

Re: Date format in master calender

its_anandrjs — Tue, 29 Aug 2017 08:10:03 GMT

You can try this way also by joining Holiday field with adding one Holiday flag to identify after joining what are holiday dates rows i data model.

LEFT JOIN ( CalendarMaster_Temp )

LOAD Date( [holidays] , 'DD-MMM-YY' ) as Date,

'1' as Holiday_Flag

FROM

[..\Holiday.xlsx]

(ooxml, embedded labels, table is Sheet1);

Re: Date format in master calender

its_anandrjs — Tue, 29 Aug 2017 08:11:51 GMT

Also one simpler way

LEFT JOIN ( CalendarMaster_Temp )

LOAD

Date( [holidays] , 'DD-MMM-YY' ) as Date,

Date( [holidays] , 'DD-MMM-YY' ) as Holiday_Date,

'1' as Holiday_Flag

FROM

[..\Holiday.xlsx]

(ooxml, embedded labels, table is Sheet1);

Re: Date format in master calender

ashis — Tue, 29 Aug 2017 08:16:33 GMT

Please see my code now.

Re: Date format in master calender

ashis — Tue, 29 Aug 2017 08:43:23 GMT

That's what I am doing.

Re: Date format in master calender

its_anandrjs — Tue, 29 Aug 2017 09:57:54 GMT

Then try this may be you have to use Floor for date format correction

LEFT JOIN ( CalendarMaster_Temp )

LOAD

DATE(Date([holidays]),'MM/DD/YYYY'),'DD-MMM-YY') AS Date

'1' as Holiday_Flag

FROM

[..\Holiday.xlsx]

(ooxml, embedded labels, table is Sheet1);

Re: Date format in master calender

its_anandrjs — Tue, 29 Aug 2017 09:59:23 GMT

Can you provide your Holiday.xlsx file only to have a look on the date field and revert to you.

Re: Date format in master calender

sasiparupudi1 — Tue, 29 Aug 2017 12:24:15 GMT

Try

CalendarMaster_Temp:
LOAD
Date,
Year(Date) AS Year,
'Q' & Ceil(Month(Date) / 3) AS Quarter,
Date(MonthStart(Date),'MMM') as Month,
Day(Date) As Day,
WeekDay(Date) AS WeekDay,
Week(Date) As Week;
Load
Date(MinDate + IterNo() -1 ) AS Date
While (MinDate + IterNo() - 1) <= Num(MaxDate);
Load
Min(Date) AS MinDate,
Max(Date) AS MaxDate
RESIDENT Performance1;

LEFT JOIN(CalendarMaster_Temp)
LOAD Date(Date#([holidays],'MM/DD/YYYY'),'DD-MMM-YY') as Date,
1 AS IsHolidayFlag
FROM
[..\Holiday.xlsx]
(ooxml, embedded labels, table is Sheet1);

Calendar:
Noconcatenate LOAD
Date,
Year,
Quarter,
Month,
Day,
WeekDay,
Week,
If(SubStringCount('$(vWeeklyHolidays)', WeekDay(Date)) OR IsHolidayFlag = 1, 1, 0) AS IsHolidayFlag,
If(SubStringCount('$(vWeeklyHolidays)', WeekDay(Date)) OR IsHolidayFlag = 1,'Non Working/Holiday', 'Working Day') AS DayType
Resident CalendarMaster_Temp;

DROP TABLE CalendarMaster_Temp;

Re: Date format in master calender

ashis — Wed, 30 Aug 2017 04:41:38 GMT

Hi Sasidhar,

I tried that too, however did not work. one thing I did and it worked for me,

I changed the date format in the CalendarMaster_Temp as the following.

DATE#(Date(Date,'DD-MMM-YY'),'DD-MMM-YY') AS Date,

Re: Date format in master calender

ashis — Wed, 30 Aug 2017 06:15:29 GMT

I tried that too, however did not work. one thing I did and it worked for me,

I changed the date format in the CalendarMaster_Temp as the following.

DATE#(Date(Date,'DD-MMM-YY'),'DD-MMM-YY') AS Date,

Re: Date format in master calender

jonathandienst — Wed, 30 Aug 2017 06:42:50 GMT

It seems like the dates are not coming on the format you think or expect*. I suggest that you create a small test app, and just load the Date without any formatting and then see what you are getting.

Is it numeric? Then you just need a Date() function to format it.

Is it text? Then you need Date(Date#(.... <format>)) to interpret and format it.

Does it have a time component? The interpretation must account for the time as well

*Excel is a problem here. The value displayed in Excel and what is returned by the Excel loader are not necessarily in the same format.