Issue with join key (full outer join) - Optimization

diwakarnahata — Thu, 12 Mar 2015 19:53:13 GMT

Hi All,

I want to optimize my data model by minimizing the # of columns used, especially the columns with a lot of distinct values.

I have a scenario where, there are two tables associated with a key column say key1.

Now, QlikView by default creates a full outer join, and the join column holds all the distinct values from the two tables combined.

Is there any way i can count the distinct values from any one of the two tables using the same join key key1?

Currently, i have to create a copy of the key1 in both the tables (which is not used for joining) for the counts.

Let me know what is recommended and if my approach is correct.

Regards,

Diwakar

Re: Issue with join key (full outer join) - Optimization

sunny_talwar — Thu, 12 Mar 2015 20:12:40 GMT

If there is a column which is unique to each of its respective tables and it is available throughout the table, then you can use that to do a distinct count. For instance if Table 1 has a column name city which is not in Table 2. Then within your text object you can write this expression: =Count(DISTINCT {<city = {'*?'}>} key1). This will only count places where city is not blank, which is available in your Table 1 only. (Table 2 will have it all blank)

You can also create your flag for each of the table in the script and use that flag in your set analysis statement (more efficient way to do it, but require manipulation in the script itself)

Table1:

LOAD xyz,

key1

1 as Flag1

FROM xyz;

Table2:

LOAD zzz,

key1,

1 as Flag2

FROM zzz;

Expression for count: =Count(DISTINCT {<Flag1 = {1}>}) key1) - > will give you distinct count of key1 from Table1

=Count(DISTINCT {<Flag2 = {1}>}) key1) - > will give you distinct count of key1 from Table2

HTH

Best,

topic Issue with join key (full outer join) - Optimization in QlikView

Issue with join key (full outer join) - Optimization

Re: Issue with join key (full outer join) - Optimization