topic Where Exists clarification in QlikView

Where Exists clarification

Fri, 23 Mar 2012 17:16:43 GMT

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);

Where Exists clarification

Jason_Michaelides — Fri, 23 Mar 2012 18:51:21 GMT

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

Where Exists clarification

jonathandienst — Fri, 23 Mar 2012 19:08:04 GMT

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