topic Search if value is found in another field in App Development

Search if value is found in another field

KarlaB — Wed, 01 Feb 2023 15:31:40 GMT

Good day!

I have a shopping table that have a field "Purchase Order", and also I have a table of purchases received with same "Purchase Order" field.

For certain reasons (as filters and extra info in both tables) i don't want them to associate, but i would like to know if there's a function as match or find so I can search if table1.PO are between table2.PO, so I can add nonreceived PO's on qlik table.

Regards

Re: Search if value is found in another field

Mark_Little — Wed, 01 Feb 2023 15:40:42 GMT

I would do something in script.

You could do a resident load of the PO Number from one table and join it to the other,

Something

JOIN (TABLE1)

LOAD
"Purchase Order"

1 as Flag

Resident TABLE2;

The flag would then let you know if the PO Number in Table 2 exists in Table 1

Re: Search if value is found in another field

QFabian — Wed, 01 Feb 2023 16:02:31 GMT

Hi @KarlaB , you can check the EXISTS function.

Exists - script function

Exists() determines whether a specific field value has already been loaded into the field in the data load script. The function returns TRUE or FALSE, so can be used in the where clause of a LOAD statement or an IF statement.

https://help.qlik.com/en-US/sense/November2022/Subsystems/Hub/Content/Sense_Hub/Scripting/InterRecordFunctions/Exists.htm

Re: Search if value is found in another field

KarlaB — Wed, 01 Feb 2023 18:05:31 GMT

That's exactly what i'm looking for, but I tried and qlik says "EXISTS is not a valid function".

On excel I would try as if(isnumber(match())) but qlik also does not recognize ISNUMBER function.

Re: Search if value is found in another field

QFabian — Wed, 01 Feb 2023 18:24:52 GMT

Dear @KarlaB , exists is a script function to use in where clause.

In Qlik to validate if a field value is a number use ISNUM.... 😉

https://help.qlik.com/en-US/sense/November2022/Subsystems/Hub/Content/Sense_Hub/Scripting/LogicalFunctions/logical-functions.htm#IsNum