8 Replies Latest reply: Feb 17, 2010 12:51 PM by satanas313 RSS

    Joins and data model

    satanas313

      Hi,

      I'm new on QlikView and I'd like to have some advices for a particular problem :

      - I created a dashboard which presents 5 indicators. This indicators works fine and use 6 tables defined in the data model. The tables are joined with inner joins. I want to build a last indicator which uses only 2 of the 6 tables. The problem is only for this indicator I don't want any supplementary join that the join between the 2 tables. If the others joins are applied the result is "too small".

      I can't create this indicator on a different dashboard and as I said, The others are good so the model seems too.

      Any explication or advice are welcome and tell me if some points need precisions.

      Thanks

        • Joins and data model

          Hi,

          A quick and dirty way of doing this without getting too complicated, and if the data is not too large would be to create 2 new tables from whats already in memory doing resident loads and using the qualify and unqualify commands.

          Newtable1:

          Qualify *;

          Unqualify NewKeyField;

          Load

          *,

          oldkeyfield as NewKeyField;

          resident oldtable1;

          unqualify *;

          and replicate for the second table.

          You should end up with two data islands. Your original and new qualified island with a single unqualified join on the NewKeyField.

          There are many ways of doing this....you may also want to look at Set Analysis,

           

          Regards,

           

          Neil

            • Joins and data model
              satanas313

              Thanks a lot for your response.

              If I build two "islands" of data how does it work in memory? I will have two sets of data really separated or does the system generates a unique data set with all possible combinations?

              For the "Set Analysis" solution, I looked but I own I don't know how to apply this to my problem. I understand the concept but I'm incompetent to build it.

              Thanks

                • Joins and data model

                  As I said it would be quick and dirty,

                   

                  When doing the resident loads, I showed in the stript load *, Keyfield. You could specify only the fields you need to cut down on memory.

                  The original suggested solution would take double the memory because you are loading the data in twice. It just depends on how much data you are loading. At moment how much memory does the qvw occupy when loaded into developer you can check it in taskmanager.

                  Regards,

                  Neil

                    • Joins and data model
                      satanas313

                      Ok I thank you for your time.

                      For my cultivation, in few words what could be the clean way to do this?

                      Smile

                        • Joins and data model

                          what formula or sums are you trying to do between these 2 tables and i might be able to make some suggestions of how to do it.

                          Regards,

                           

                          Neil

                            • Joins and data model
                              satanas313

                              The formula is really simply. It's just a division between 2 sum. To my mind It's "only" a data problem and a join problem in particular.

                              The others joins limits the records of the 2 tables and this causes wrongs sums (wrong because of number of rows). For example if only the 2 tables were joined, we would have 1 000 rows but because of others joins we have 500 rows. So the sums aren't correct...

                              Thank you for your help

                               

                                • Joins and data model

                                  You may have to use Set analysis a very simple example below

                                  Here is an example for doing total gross profit

                                  SUM( {1} Margin) / SUM( {1}Sales)

                                  {1} is the set operator and denotes a full set of data.

                                  This will sum all the margin regardless of selection and divide by the sum of all sales regardless of selection.

                                  You may also need to incorporate If Statements eg

                                  SUM( {1} IF(Category = 'Shoes', Margin) ) / SUM( {1} IF(Category = 'Shoes', Sales) )

                                  To give total Gross profit for shoes,

                                   

                                  Regards,

                                   

                                  Neil

                                    • Joins and data model
                                      satanas313

                                      Ok thank a lot for your response I understand the principle.

                                      To take your example, in my case, Margin comes from Table1 and Sales from Table2.

                                      Does set analysis applies to my problem? If I use "{1}" do I have all Margin and all Sales related or do I have all Margin and all Sales like a full outer join?

                                      Tell me if my question needs more explanations.

                                      Thanks