Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
diwakarnahata
Contributor

Issue with join key (full outer join) - Optimization

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

Tags (1)
1 Reply

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

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,

S