Exists and Not Exists is not working as expectec

[vikasshana]

Hi,

I do have the below example excel data which I'm loading into my application.

Sheet1:

Sheet2:

I've loaded both the sheets and below is the code and output.

Test1:
LOAD
A
FROM [lib://AttachedFiles/Test.xlsx]
(ooxml, embedded labels, table is Sheet1);

Test2:
LOAD
A1 as A 
FROM [lib://AttachedFiles/Test.xlsx]
(ooxml, embedded labels, table is Sheet2);

Output:

A

1

2

3

4

Now my requirement is I would like to take the resident of test2 and compare it with test1 and load the data exists and Non exists data into a new table.

Expected Output for exists

A	Status
1	Exists
2	Exists

Expected Output for not exists

A	Status
4	Not Exists

Regards,

Vikas

[Vegar]

In Qlik Sense, when loading two data loads containing identical set of field names, the loads will automatically concatenate into one table. So in your script, the data from Test2 will autoconcatenate into your Test1 table.

I suggest you do the flag logic in the first loads like this. 

Test:

LOAD A, 'Exists' as Status FROM Test1Source;

Concatenate (Test)

LOAD A1 as A, if(exists(A,A1), 'Exists', 'Not Exists') as Status FROM Test2Source;

[AshutoshBhumkar]

Hi,

Below is the example. Let me know if that suffice your requirement or not.

E.g

Table1:
Load * Inline [
A
1
2
3
];

Table2:
Load * Inline [
A1
1
2
4
];

TableforExist:
Load
A1 as Dimension,
'Exists' as Status
Resident Table2 where Exists(A,A1);

Concatenate

TableForNotExist:
Load
A1 as Dimension,
'Not Exists' as Status
Resident Table2 where not Exists(A,A1);

Output:

https://help.qlik.com/en-US/sense/August2021/Subsystems/Hub/Content/Sense_Hub/Scripting/InterRecordF...

Thanks,

Ashutosh

[vikasshana]

Hi Vegar,

Thanks for your suggestion, I tried and I get the below Output.

A	Status
1	Exists
2	Exists
3	Exists
4	Not Exists

But I'm expecting below output.

A	Status
1	Exists
2	Exists
4	Not Exists

[Vegar]

Try this: 

Test1:
Load A
FROM [lib://AttachedFiles/Test.xlsx]
(ooxml, embedded labels, table is Sheet1);

Test2:
NoConcatenate
Load
   A1 as A,
   if(exists(A,A1), 'Exists', 'Not Exists') as Status
FROM [lib://AttachedFiles/Test.xlsx]
(ooxml, embedded labels, table is Sheet2);

exit script;

(You might want to drop Test1 using DROP TABLE Test1; when script is run)

[abhijitnalekar]

Great @Vegar ,

Just one more point want to add one more point.

Uncheck include null values checkbox.