Qlik Community

Qlik Design Blog

All about product and Qlik solutions: scripting, data modeling, visual design, extensions, best practices, etc.

Employee
Employee

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.

Dual script.png

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

AgeBucket list box.png

Dual chart.png

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.

Entire script.png

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.

Person table.png

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

24 Comments
Specialist
Specialist

Jennell

Great post. Thanks for explain this for us.

Márcio

0 Likes
4,171 Views
Specialist III
Specialist III

Its a very hot day and I'm tired so maybe I'm missing something

But doesn't left join do the same as exists.

And hasn't 11 and 12 already been loaded in the data model (under an inline load). Or does inline loads not count with exists.

4,171 Views
Specialist III
Specialist III

This is a good post. Thanks a lot.

Would it be OK to consider the below example as another scenario of exists():

We have a function called Match which we can use in the where clause but with hard coded matching values.

for example:

load City, employeeID from employee where Match(City,'Newark','Wilmington','Milford');

Now assume that Client requirement says that the matching City name would change in course of time.

then instead of hard coding it, it can be dynamically read from a spread sheet which will have those City names that clients want to include in the where clause criteria.

so now I can load a table with all those City names to be added to where clause and use Exists function in the where clause like below.

MatchCity:

load CityNames from Cities.xls; // the xls have Newark, Wilmington and Milford

Maintab:

load City, employeeID from employee where exists(CityNames,City);

Drop table MatchCity; // we no longer need the table

Thanks..

0 Likes
4,171 Views

I often use Exists as when loading from a qvd the load remains optimised

4,171 Views
Specialist III
Specialist III

Hi Bill,

while using Exists we will be using it in the where clause and I think when we load from a qvd and use a where clause then the qvd load is no more optimize. Can you please clarify from your end?

0 Likes
4,171 Views

Yup, a load with a single where exists remains optimised.

A load with any other kind of where is not optimised.

4,171 Views