Dual & Exists – Useful Functions

[Jennell_McIntire]

I find that the Dual and Exists functions are useful functions that I often use in my script.  The Dual function allows me to represent a string field numerically, making it ideal for sorting purposes.  The Exists function, used in conjunction with the Where clause, allows me to only load related data into my data model.

The Dual function can be used in the script and chart expressions.  I tend to use it in the script when I need the ability to sort a string field numerically.  For example, in the script snippet below, I created age buckets and I want to be able to sort them in a list box and a chart so I used the Dual function to give each bucket a numeric value.

By using the Dual function, I am able to sort the AgeBucket list box and the AgeBucket dimension in my chart numerically.

The Exists function is used to determine whether a “specific field value exists in a specified field of the data loaded so far.”  I often use this with the Where clause to load data only when the specified data has already been loaded in the data model.  For example, in the script below the Age and AgeBucket fields are loaded only if the PersonID has already been loaded in the data model.

Notice in the AgeTemp table that there are ages listed for PersonID 11 and 12 but since those IDs were not loaded in the data model (in the People table), they are excluded by the Where Exists(PersonID) clause.  The table below shows the data that was loaded.

I often blog about functions used in QlikView but I find the Dual and Exists to be two functions that I use often so that I can present data in a desired sort order and can keep my data model free of unassociated data.  If you would like to learn more or see an example application, check out my technical brief.

Thanks,

Jennell

[#2]

The dual example is great and it will be very useful for me, but I have a problem with the Exist example. In the example you give us, the only thing that is limiting the records loaded is the left join. If you change the left join for a join, then the result will include PersonID 11 and 12, meaning the Exist statement doesn't do anything. This is because the where Exists(PersonID) clause checks for all rows loaded, including the rows loaded from the AgeTemp table (which also has a field named PersonID) At the time you join the two tables, the field PersonID has all the values loaded from the People table + the values loaded from the AgeTemp table. Someone please correct me if I'm wrong.

Regards,

Diego

[#3]

Yes, I agree, the example is misleading, the exists doesn't do anything in that case as you correctly pointed out.

[ankitbisht01]

Thank You , nicely explained

[beck_bakytbek]

Great post, thanks for sharing