Need to create Master Calendar using WEEK data

[Buchannagari]

Hi,

I have a excel data in that order date column is not there 

so, I want to create master calendar by using WEEK column or by using DATES column 

for reference 

so how to get min and max date from WEEK and DATES columns 

i am using below Master calendar script 

QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);
Temp:
Load
min(OrderDate) as minDate,
max(OrderDate) as maxDate
Resident Smart_Orders;
Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;
TempCalendar:
LOAD
$(varMaxDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
MasterCalendar:
Load
TempDate AS OrderDate,
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day,
date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,
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;
// exit SCRIPT;

can anyone help me here

Thanks & Regards,

Buchannagari.

[vinieme12]

temp:

Load 

Week,DATES

FRom XYZsource;

NOCONCATENATE

temp2:

Week,DATES

Resident temp Order by Week ASC;
Drop table temp;

let vminDate = makedate(year(today()),month(Date#(trim(subfield(peek('DATES',0,'temp2'),'-',1)),'DD MMM')),Day(Date#(trim(subfield(peek('DATES',0,'temp2'),'-',1)),'DD MMM'));

let vmaxDate = makedate(year(today()),month(Date#(trim(subfield(peek('DATES',-1,'temp2'),'-',2)),'DD MMM')),Day(Date#(trim(subfield(peek('DATES',-1,'temp2'),'-',2)),'DD MMM'));

[Buchannagari]

Hi,

Some how it is not working fine 

Actually i have a excel data(PFA) from that want to pull all the columns using for each 

done with that .

so , now want to create master by using WEEK and also with DATES columns

with the above script i am getting error

please look at attachment once

Regards

[vinieme12]

temp:
LOAD
"WEEK",
DATES

FROM [lib://AttachedFiles/For Loop Example - Copy.xlsx]
(ooxml, embedded labels, table is US)
Where len(WEEK);

NoConcatenate
temp2:
Load WEEK,DATES,date#(trim(subfield(DATES,'-',1))&year(today()),'DD mmmYYYY') as from, date#(trim(subfield(DATES,'-',2))&year(today()),'DD mmm') as to
Resident temp
ORDER BY WEEK ASC;
drop table temp;

let vMinDate = date(date#(peek('from',0,'temp2'),'DD MMMYYYY'));
let vMaxDate = date(date#(peek('to',-1,'temp2'),'DD MMMYYYY'));

trace min date '$(vMinDate)';
trace max date '$(vMaxDate)';

MasterCalendar:
Load
TempDate AS DATE,
week(TempDate) As Week,

Year(TempDate) As Year,
Month(TempDate) As Month,
monthname(TempDate) As monthyear,
Day(TempDate) As Day,
'Q' & ceil(month(TempDate) / 3) AS Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay
;

//=== Generate a temp table of dates ===
LOAD
Date(date#('$(vMaxDate)','YYYY-MM-DD') + RecNo()) AS TempDate
AUTOGENERATE date#('$(vMaxDate)','YYYY-MM-DD') - date#('$(vMinDate)','YYYY-MM-DD');

[Buchannagari]

Hi,

Thanks for the reply 

but while using script i am getting error 

min date is not fetching 

can you please help me here 

the script i have used

temp:
LOAD
"WEEK",
DATES

FROM [lib://DataFiles/For Loop Example.xlsx]
(ooxml, embedded labels, table is US)
Where len(WEEK);

NoConcatenate
temp2:
Load WEEK,DATES,date#(trim(subfield(DATES,'-',1))&year(today()),'DD mmmYYYY') as from, date#(trim(subfield(DATES,'-',2))&year(today()),'DD mmm') as to
Resident temp
ORDER BY WEEK ASC;
drop table temp;

let vMinDate = date(date#(peek('from',0,'temp2'),'DD MMMYYYY'));
let vMaxDate = date(date#(peek('to',-1,'temp2'),'DD MMMYYYY'));

trace min date '$(vMinDate)';
trace max date '$(vMaxDate)';
TempCalendar:
LOAD
Date(date#('$(vMaxDate)','YYYY-MM-DD') + RecNo()) AS TempDate
AUTOGENERATE date#('$(vMaxDate)','YYYY-MM-DD') - date#('$(vMinDate)','YYYY-MM-DD');

MasterCalendar:
Load
TempDate AS DATE,
week(TempDate) As Week,

Year(TempDate) As Year,
Month(TempDate) As Month,
monthname(TempDate) As monthyear,
Day(TempDate) As Day,
'Q' & ceil(month(TempDate) / 3) AS Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;
Exit script;

//=== Generate a temp table of dates ===
// LOAD
// Date(date#('$(vMaxDate)','YYYY-MM-DD') + RecNo()) AS TempDate
// AUTOGENERATE date#('$(vMaxDate)','YYYY-MM-DD') - date#('$(vMinDate)','YYYY-MM-DD');

Exit Script;

Regards,

[vinieme12 · Accepted solution]

You need to replace 'YYYY-MM-DD'  with the date format set in your app which seems to be 'MM/DD/YYYY'

[Buchannagari]

It  is working  fine 

Thank you