topic Duplicate values are not considering while using where exists in App Development

Duplicate values are not considering while using where exists

nihhalmca — Mon, 13 Dec 2021 18:57:23 GMT

Hi All -

My requirement is as follows

Source:

Period	Name	Sales
Q12021	A	100
Q12021	B	200
Q22021	N	20
Q22021	D	50
Q32021	M	150
Q32021	H	250
Q42021	L	200
Q42021	C	500

Script:

Result:

Expected Result:

Period	Name	Sales
Q12021	A	100
Q12021	B	200
Q22021	N	20
Q22021	D	50
Q32021	M	150
Q32021	H	250
Q42021	C	500
Q42021	L	200

Duplicate values are not considering while using where exists, is it possible to achieve expected results.

Appreciate your help, thanks

Re: Duplicate values are not considering while using where exists

Or — Mon, 13 Dec 2021 19:05:43 GMT

As the Qlik documentation highlights:

"You can also use Not Exists() to determine if a field value has not been loaded, but caution is recommended if you use Not Exists() in a where clause. The Exists() function tests both previously loaded tables and previously loaded values in the current table. So, only the first occurrence will be loaded. When the second occurrence is encountered, the value is already loaded."

In other words, for your example, all Q42021 values will be loaded from the first load, and then the first value for each period (excluding Q42021) will be loaded from the second table. This is correct behavior.

See the documentation for a suggestion on how to work around this that you may be able to use.

https://help.qlik.com/en-US/qlikview/May2021/Subsystems/Client/Content/QV_QlikView/Scripting/InterRecordFunctions/Exists.htm

[...]

To be able to get all values for Lucy you need to change two things:

Add a preceding load to Employees where you rename Employee to Name.

Load Employee As Name, ID, Salary;
Change the Where condition in Citizens to:

not Exists (Name, Employee).

Re: Duplicate values are not considering while using where exists

nihhalmca — Wed, 15 Dec 2021 13:51:38 GMT

Thank you.