if(exists()) in load script

[VaibhavD]

Hi All,

I am new to Qlik and that's why i apologies in advance if this is very basic question.

I have list of few identifier (ID) in Table A. While loading a bigger Table B I want to check if ID exists in Table A i want to set a flag as 'true' otherwise as 'false'. for this i am using exists() function is load script,

"BasisProduct":
LOAD
TEXT("Produkt ID") as BasisProduct_ID
FROM [lib://010420/Base Products.xlsx]
(ooxml, embedded labels, table is Tabelle1);

"Software_Product":
LOAD
TEXT(ID) As ID,
Name,
if(exists (BasisProduct_ID, ID), 'true', 'false') as isBasisprodukt,
FROM [lib://010420/SoftwareProducts.xlsx]
(ooxml, embedded labels, table is Main);
drop table "BasisProduct";

Issues is that BasisProduct table contain 8 IDs however in Software_Product table flag is set 'true' only for three IDs out of 8.

I have already checked if there are leading or trailing spaces in IDs but that is not the case.

Can someone help me to identify the issue or if this approach is completely wrong, can you please suggest correct way of doing it.

many thanks in advance

[lennart_mo · Accepted solution]

Hi @VaibhavD,

In both LOAD Statements you perform a transformation for ID, even if it is only text(ID).

Try using Text(ID) in your WHERE Statement as well, as Qlik might try and compare a string to a number in your current script.

Kind regards

Lennart

[VaibhavD]

@lennart_mo  Thank you for quick response. it worked like a charm.