Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

christhames
New Contributor

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;

1 Reply
zebhashmi
Valued Contributor

Re: Qlik Sense: Unable to create key field in IntervalMatch dataset

Can't see any thing big

// 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;