Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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
MVP
MVP

Where Exists clarification

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
2 Replies
jason_michaelid
Honored Contributor II

Where Exists clarification

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

MVP
MVP

Where Exists clarification

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
Community Browser