Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

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;

2 Replies
Not applicable

Re: Equating items closed by comparing current & prior day in script

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

balrajahlawat
Esteemed Contributor

Re: Equating items closed by comparing current & prior day in script

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!

Community Browser