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

[its_anandrjs]

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

[sasiparupudi1]

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; 

[ashis]

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,

[ashis]

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,

[jonathandienst]

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.