Qlik Sense: Unable to create key field in IntervalMatch dataset
Hi! I'm experiencing an issue with inserting a key field into a table involving an IntervalMatch. Essentially, I create a list of dates over the last nine months and evaluate whether or not each process was open at that point in time using an IntervalMatch (creating the table "Proc_Temp"). This generates around 7 million rows, as expected (no issues up to this point). However if I then try to load that data into another table ("Key_Load"), the number of rows in the new table seems to go up indefinitely (tens of millions) for no apparent reason and it gets so large in the end that I just have to cancel the load.
What am I doing wrong? I'd have thought that for loading two fields ("TOJ Unique Journey Key" and "Time Series Date") and creating a key out of them, the number of rows in the new table should be identical to that of the old table. I have tried even just loading a single field from the interval matched table ("Proc_Temp") into a new table and it still generates an enormous number of rows. How can I stop this from happening? All I need to end up with is a table containing the two aforementioned fields and a unique key field between them (which can be anything; autonumber, a simple concatenation, whatever works basically). I have pasted my script below:
// Load of Process data table Process_Data: LOAD "Customer - Key" as "Process Customer Key", "Work Order - Lead Order (Key)" as "Process Lead Work Order", "Customer - Key" & '/' & "Work Order - Lead Order (Key)" as "Process Unique Journey Key", "Customer - Key" & '/' & "Work Order - Lead Order (Key)" as "TOJ Unique Journey Key", "CRM_IR Created Date - Key_Min" as "Process Created Date", date(if("Open" = 1,today(1),if(date("Journey end date_Max")<date("CRM_IR Created Date - Key_Min"),"CRM_IR Created Date - Key_Min","Journey end date_Max"))) as "Process End Date", // create the report date FileTime() as ProcReportDate FROM [lib://$(vProdOrDevFilePath)/Trends Journey Data.csv] (txt, codepage is 1252, embedded labels, delimiter is ',', msq);
// Load of temp dates Let varMinDate = num(Date(AddMonths(Today(1),-9))); Let varMaxDate = num(Date(Today(1)));
Pre_Time_Temp: LOAD $(varMinDate) + Iterno()-1 as Num, Date#(Date($(varMinDate) + IterNo() - 1),'DD/MM/YYYY') as "Series Date" AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
Time_Temp: LOAD Timestamp("Series Date") as "Time Series Date" RESIDENT Pre_Time_Temp;
DROP TABLE Pre_Time_Temp;
// Interval matching of processes against dates in which they were open Proc_Temp: LOAD "TOJ Unique Journey Key", timestamp(num("Process Created Date") + 0.5) as startdate, timestamp(num("Process End Date") + 0.5) as enddate RESIDENT Process_Data; // Interval join with TOJ list of dates INNER JOIN IntervalMatch ("Time Series Date") LOAD startdate, enddate RESIDENT Proc_Temp;
Key_Load: LOAD DISTINCT "TOJ Unique Journey Key", "Time Series Date", "TOJ Unique Journey Key" & '-' & "Time Series Date" as "TOJ Key" RESIDENT Proc_Temp;
//Drop temporary tables DROP TABLE Time_Temp; DROP Table Proc_Temp;
// Load of Process data table Process_Data: LOAD "Customer - Key" as "Process Customer Key", "Work Order - Lead Order (Key)" as "Process Lead Work Order", "Customer - Key" & '/' & "Work Order - Lead Order (Key)" as "Process Unique Journey Key", "Customer - Key" & '/' & "Work Order - Lead Order (Key)" as "TOJ Unique Journey Key", "CRM_IR Created Date - Key_Min" as "Process Created Date", date(if("Open" = 1,today(1),if(date("Journey end date_Max")<date("CRM_IR Created Date - Key_Min"),"CRM_IR Created Date - Key_Min","Journey end date_Max"))) as "Process End Date", // create the report date FileTime() as ProcReportDate FROM [lib://$(vProdOrDevFilePath)/Trends Journey Data.csv] (txt, codepage is 1252, embedded labels, delimiter is ',', msq);
// Load of temp dates Let varMinDate = Date#(Date(AddMonths(Today(1),-9))); Let varMaxDate = Date#(Date(Today(1)));
Pre_Time_Temp: LOAD $(varMinDate) + Iterno()-1 as Num, Date#(Date($(varMinDate)+ IterNo() - 1),'DD/MM/YYYY') as "Series Date" AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
Time_Temp: LOAD Timestamp("Series Date") as "Time Series Date" RESIDENT Pre_Time_Temp;
DROP TABLE Pre_Time_Temp;
// Interval matching of processes against dates in which they were open Proc_Temp: LOAD "TOJ Unique Journey Key", timestamp(num("Process Created Date") + 0.5) as startdate, timestamp(num("Process End Date") + 0.5) as enddate RESIDENT Process_Data; // Interval join with TOJ list of dates INNER JOIN IntervalMatch ("Time Series Date") LOAD startdate, enddate RESIDENT Proc_Temp;
Key_Load: LOAD DISTINCT "TOJ Unique Journey Key", "Time Series Date", "TOJ Unique Journey Key" & '-' & "Time Series Date" as "TOJ Key" RESIDENT Proc_Temp;
//Drop temporary tables DROP TABLE Time_Temp; DROP Table Proc_Temp;
