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;
