Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
suren946
Contributor III
Contributor III

Master Calendar

Hi All,

I have multiple date in single table as below

CATask:

LOAD

     if(len(trim([RITM Number]))>0,[RITM Number]) as RITM,

     if(len(trim([CATASK Number]))>0,[CATASK Number]) as CATask,

     if(len(trim(Queue))>0,Queue) as Queue,

     if(len(trim(Complexity))>0,Complexity) as Complexity,

     Date([Packaging / UAT Task Received date], 'MM/DD/YYYY') as [Packaging Task],

     Date([APF Notified Date],'MM/DD/YYYY') as [APF Notified Date],

     Date([APF ETA Date], 'MM/DD/YYYY') as [APF ETA Date],

     Date([APF Package Delivered Date],'MM/DD/YYYY') as [APF Package Delivered Date],

FROM

xls;

Created multiple Master calendars as below:

[Packaging / UAT Task Received date]:


Load


Date(Temp) as [Packaging Task],

Month(Temp) as [Packaging Task Month],

year(Temp) as [Packaging Task Year]

;

load Mindate+IterNo()-1 as Temp,

Maxdate

While Mindate+IterNo()<= Maxdate ;


Load date( min([Packaging Task]),'MM/DD/YYYY') as Mindate,

   date( Max([Packaging Task]),'MM/DD/YYYY') as Maxdate

Resident CATask;



[APF Notified Date]:

load Temp as [APF Notified Date],

Month(Temp) as [APF Notified Date Month],

year(Temp) as [APF Notified Date Year]

;

load Mindate+IterNo()-1 as Temp,

Maxdate

While Mindate+IterNo()<=Maxdate ;


Load date( min([APF Notified Date]),'MM/DD/YYYY') as Mindate,

   date( Max([APF Notified Date]),'MM/DD/YYYY') as Maxdate

Resident CATask;

 

 

[APF ETA Date]: 

load Temp as [APF ETA Date],

Month(Temp) as [APF ETA Month],

year(Temp) as [APF ETA Year]

;


load Mindate+IterNo()-1 as Temp,

Maxdate

While Mindate+IterNo()<=Maxdate ;


Load date( min([APF ETA Date]),'MM/DD/YYYY') as Mindate,

   date( Max([APF ETA Date]),'MM/DD/YYYY') as Maxdate

Resident CATask; 




[APF Package Delivered Date]:


load Temp as [APF Package Delivered Date],

Month(Temp) as [APF Package Delivered Date Month],

year(Temp) as [APF Package Delivered Date Year]

;


load Mindate+IterNo()-1 as Temp,

Maxdate

While Mindate+IterNo()<=Maxdate ;


Load date( min([APF Package Delivered Date]),'MM/DD/YYYY') as Mindate,

   date( Max([APF Package Delivered Date]),'MM/DD/YYYY') as Maxdate

Resident CATask;



I also want to create one common calendar as below but resulting synthetic keys as many dates. Please help me with code to create common master calendar with out SKs.


LOAD floor(if(len(trim(S.No))>0,S.No)) as SNO,

     if(len(trim([RITM Number]))>0,[RITM Number]) as RITM,

     if(len(trim([CATASK Number]))>0,[CATASK Number]) as CATask,

     if(len(trim(Queue))>0,Queue) as Queue,

     if(len(trim([Application Name]))>0,[Application Name]) as AppName,

     Date([Packaging / UAT Task Received date], 'MM/DD/YYYY') as CommonDate,

     Date([Packaging / UAT Task Received date], 'MM/DD/YYYY') as Flag,

     if(len(trim(Complexity))>0,Complexity) as Complexity

    

FROM

DM.JPG

 

0 Replies