Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
It sounds like NOT EXIST function. You can first load the Current_Day & Prior_Day table seperately
Tmp1:
LOAD
itemID,
itemID As itemID1,
itemCCY
FROM 'lib://Source/Current Day/current_day.xlsx'(ooxml, embedded labels, table is Data);
NoConcatenate
Tmp2:
LOAD
itemID,
itemCCY
FROM 'lib://Source/Prior Day/prior_day.xlsx' (ooxml, embedded labels, table is Data)
WHERE Not Exists(itemID1,itemID );
And the other way around. Hope it makes sense
add a flag in these two tables like this?
Table1:
LOAD
itemID,
'Current' as Flag,
itemCCY
FROM 'lib://Source/Current Day/current_day.xlsx'(ooxml, embedded labels, table is Data);
CONCATENATE
LOAD
itemID,
'Prior' as Flag,
itemCCY
FROM 'lib://Source/Prior Day/prior_day.xlsx' (ooxml, embedded labels, table is Data);
Then use this flag in your expression to differentiate Current & Prior...Hope this will help!