Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Where Exists clarification

I  understand how the where exists works, but what I have now seen for the firsttime is a where exists with 2 fields in it, Where exists (field1,field2).  Can someone tell me what the where exists is doing below?

Material_MATNR is a field in the Fact table and then the 1st block of code below uses it to create ActiveMaterial

Left Join (Fact)
LOAD distinct
%KEY_VBRP,
If(isNull(Material_SMATN),Material_MATNR,Material_SMATN) & '/' &[SalesOrganization_VKORG] as %ACTIVEMATNR%VKORG,
If(isNull(Material_SMATN),Material_MATNR,Material_SMATN) as ActiveMaterial
Resident Fact;

Now Fact table has Material_MATNR and ActiveMaterialand then the code below comes in next. So is it saying, load this code where Material_MATNR and ActiveMaterialexists while it loads Material_MATNR as ActiveMaterialat the same time?

Material:
LOAD
Material_MATNR as ActiveMaterial,
[Language Key_SPRAS],
[Material Description_MAKTX]
FROM\\ghos2280\repository$\03.MASTER\MATERIAL\DATA\MAKT.qvd (qvd)
WHERE EXISTS (ActiveMaterial,Material_MATNR);

1 Solution

Accepted Solutions
jonathandienst
Partner - Champion III
Partner - Champion III

Sean

With 2 parameters, Exists will evaluate the second parameter (which can be a field or an expression) and return true if the value exists in the field name in the first parameter.

In your example, the second load statement will only load records where the Material_MATNR was already loaded by the first load statement (as ActiveMaterial). The script is functionally the same as prefixing the second load with a Left Join or Left Keep qualifier.

Regards

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

2 Replies
Jason_Michaelides
Luminary Alumni
Luminary Alumni

This is explained fully in the F1 help. Basically, records will be loaded into the Material table from MAKT.qvd only where Material_MATNR exists in the previously loaded ActiveMaterial field.

Hope this helps,

Jason

jonathandienst
Partner - Champion III
Partner - Champion III

Sean

With 2 parameters, Exists will evaluate the second parameter (which can be a field or an expression) and return true if the value exists in the field name in the first parameter.

In your example, the second load statement will only load records where the Material_MATNR was already loaded by the first load statement (as ActiveMaterial). The script is functionally the same as prefixing the second load with a Left Join or Left Keep qualifier.

Regards

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein