1 Reply Latest reply: Feb 28, 2018 1:14 PM by Jahanzeb Hashmi RSS

    Qlik Sense: Unable to create key field in IntervalMatch dataset

    Chris Wooldridge

      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;

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

          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;