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)


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


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


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


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