1 Reply Latest reply: Mar 12, 2015 4:17 PM by Sunny Talwar RSS

    Issue with join key (full outer join) - Optimization

    Diwakar Nahata

      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

          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