Not able to set data picker in date filter due to the date format

[swetha54]

Hi All,

In my data, date format is like DD-MMM-YY, ex. 31-JAN-18.

I am trying to set date picker option using master calendar in date filter,but it is not working.

Kindly help me on this.

[jonathandienst]

Please supply details about what you are trying to do. Your post is too vague to assist you.

[swetha54]

Sorry! 

Please find below details.

Tried with below code but some vague error is coming as field 'a' not found.

as i said in my post, date format is like DD-MMM-YY. Is this a problem, if yes can you please suggest how to convert the date format first. Thanks in advance

QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);

Temp:
Load
min([INV_REPORT_DATE]) as minDate,
max([INV_REPORT_DATE]) as maxDate
Resident INVENTORY_REPORT;

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;

TempCalendar:
LOAD
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

MasterCalendar:
Load
TempDate AS [INV_REPORT_DATE],
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day,
ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;

[shiveshsingh]

When I'm trying below script, it is working.

INVENTORY_REPORT:
load * inline
[INV_REPORT_DATE
1/1/2018
12/1/2018];

QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);

Temp:
Load
min([INV_REPORT_DATE]) as minDate,
max([INV_REPORT_DATE]) as maxDate
Resident INVENTORY_REPORT;

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;

TempCalendar:
LOAD
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

MasterCalendar:
Load
TempDate AS [INV_REPORT_DATE],
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day,
ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;

Can you check data in your INVENTORY_REPORT table?

[swetha54]

Thanks Shivesh.

this code is working as INV_REPORT_DATE format is DD/MM/YYYY

But in my case the format is DD-MMM-YY. Can you please suggest how this will work for my case

[shiveshsingh]

Hi

Can you share INV_REPORT_DATE data?

You can also convert date before using it in calendar.

[jonathandienst]

TempDate will be created in the default format for your environment. Do you want to change that to DD/MM/YYYY? Just add a format string:
Date($(varMinDate) + IterNo() - 1, 'DD/MM/YYYY') as TempDate

I don't see anything in that script that is sensitive to the default date format.

[jonathandienst]

Or 'DD-MM-YYYY' as required

[swetha54]

Hi,
Thanks for your reply!
INV_REPORT_DATE format is coming like DD-MMM-YY.
i loaded this data from a file, how to convert data before using it in calendar.

[jonathandienst]

To interpret the date, use Date# during load

Date#(myDateField, 'DD-MMM-YYYY')