Equating items closed by comparing current & prior day in script
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;