2 Replies Latest reply: Jun 16, 2016 8:35 PM by Nhu Ngo RSS

    Equating items closed by comparing current & prior day in script

    Brandon1 Hecker

      Hi guys first time post, and new user to Qlik! I am making a graph to show number of items closed prior day by CCY.

       

      I have the below script to compare the itemID from 2 files: current_day  & prior_day. It works fine, and allows me to count(closed); however, I need it to be more dynamic.

       

      The idea is: if an item is on your current_day report and you resolve it, then tomorrow that item is not on your current_day report but should be on your prior_day.
      If you flip it: items not on the prior_day report, but show on the current_day report are new items that should not be counted as resolved.

       

      If <itemID> is on <prior_day>, and NOT on <current_day> (this would represent an item was closed, will need to be counted)
      If <itemID> is on <current_day>, and NOT on <prior_day> (this would be a new item, and should not be counted)

       

      Table1:
      LOAD
      itemID,
      itemCCY
      FROM 'lib://Source/Current Day/current_day.xlsx'(ooxml, embedded labels, table is Data);

       

      CONCATENATE
      LOAD
      itemID,
      itemCCY
      FROM 'lib://Source/Prior Day/prior_day.xlsx' (ooxml, embedded labels, table is Data);

       

      INNER JOIN (Table1)
      LOAD *
      WHERE "closed";

       

      LOAD
      itemID,
      if(count(itemID)<2,-1) as "closed"
      RESIDENT Table1
      GROUP BY itemID;