topic Re: Check if record exists in App Development

Check if record exists

noahfels — Fri, 29 Oct 2021 11:38:39 GMT

I have a table looking like this (without column C):

Now I want to create column C which indicates wheter a Material in A has at least one Material(Child) in B starting with a K.

As you can see in row 3, M20 has a K* Material(Child), therfore, all records with M20 should have a 1 (yes) in column C.

Same for M22, which has two K* Material(Child).

How do I generate this column C in the script?

Re: Check if record exists

Saravanan_Desingh — Fri, 29 Oct 2021 19:51:53 GMT

Try this,

tab1: LOAD * INLINE [ Material(Parent), Material(Child) M20, R00 M20, K01 M20, L00 M21, R00 M21, L00 M22, K20 M22, K30 ]; Left Join(tab1) LOAD [Material(Parent)], If(Index(Concat(DISTINCT [Material(Child)] Like 'K*'),'-1'),1,0) As [Has_K*_Child] Resident tab1 Group By [Material(Parent)];

Re: Check if record exists

noahfels — Wed, 03 Nov 2021 13:15:47 GMT

Thanks a lot this works perfectly.
However I do not quite understand why Concat returns -10 if there is a K*Child.
Furthermore, is the if-statement in Qlik always true when the condition is any number except for 0 or how does it work?

Re: Check if record exists

Saravanan_Desingh — Wed, 03 Nov 2021 16:06:41 GMT

I am concatenating the conditional statement here ([Material(Child)] Like 'K*'). So this will return either -1 or 0.