Date format in master calender

[ashis]

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

[jonathandienst]

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.

[its_anandrjs]

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);

[ashis]

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

[ashis]

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.

[its_anandrjs]

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);

[its_anandrjs]

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);

[ashis]

Please see my code now.

[ashis]

That's what I am doing.

[its_anandrjs]

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);