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

[Marcio_Campestrini]

Jennell

Great post. Thanks for explain this for us.

Márcio

[robert99]

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.

[sudeepkm]

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..

[Anonymous]

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

[sudeepkm]

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?

[Anonymous]

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

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

[robert99]

OK but if left join is used does where exist achieve anything

It seems also that left keep is speed (of loading) wise better than where exists

Are you saying that you would use where exists rather than left keep when loading from QVD tables

[Anonymous]

Left joins on QVD loads can also remain optimised.

I personally like where exists, as does Jennell McIntire who wrote the blog post.

If you prefer left join then go for it.

[#10]

Thanks Jennell.

Personally I uses Exist clause during loading. I found interesting of using Dual, I will try to use it in my scripts.

- Arindam

[#11]

Thanks for this wonder effort:-)