Discussion Board for collaboration related to QlikView App Development.
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
