4 Replies Latest reply: Aug 18, 2015 7:37 AM by Robin Luiten RSS

    Joining three tables with related dimensions

      Hello everyone,

       

      For my organization I'm creating a new dashboard with customer prices. I have three tables I'm willing to join: PrijslijstXL, Nettoprijzen and DebiteurenConditieGegevens. Since some dimensions these tables are related I've therefore pictured creating a triangular model. However, after reloading Qlikview reports that one more loops are detected in the database structure. The result is visible in the first link.

       

      http://nl.tinypic.com/r/2w4en2w/8

       

      The looping clearly results because of the overlapping dimensions. In order to fix this issue I've tried two possible solutions. The first one is creating a new table with all the related dimensions and deleting them from their original table. Unfortunately this solution does not fix the issue because now the tables won't communicate with one another. Selecting a value in a dimension from one table, excludes all other values in the dimensions from the other tables. The result is visible in the second link.

       

      http://nl.tinypic.com/r/29xi0dt/8

       

      The second possible solution is creating a new table with just the two dimensions that would appear in the sync table seen in the first link. But here also one or more loops are detected. The result is visible in the third link.

       

      http://nl.tinypic.com/r/21b3x28/8

       

      I want to ask if someone ever experienced a similar situation and has a solution for my problem. Thank you in advance.

        • Re: Joinging three tables with related dimensions
          Srikanth P

          Concatenate the tables is the best option.

            • Re: Joinging three tables with related dimensions

              Thank you for your reply.

               

              Unfortunately this does not provide the solution I'm looking for. I need to be able to place both related and unrelated data in - for instance - a straight table. Now I can't combine unrelated dimensions on a single row in a table. I also tried several 'join' inputs in script but they won't provide me a solution as well.

               

              Any other ideas?

            • Re: Joining three tables with related dimensions
              Peter Cammaert

              Please describe how exactly you want selections to work in your preferred QVW solution. QlikView works by showing all information that is related to a specific selection, and hiding the remainder. You don't want that, I presume?

               

              Peter

                • Re: Joining three tables with related dimensions

                  Thank you for your reply Peter. You are right, I don't want unrelated data to hide but instead i want to show an empty cell if there is no related value. For instance, if I select 10 products and 1 customer who has 5 of these products priced, I want to show 10 products with 5 prices and 5 empty cells. If another customer has 7 prices, I want to show 10 products with 7 prices and 3 empty cells. Do you have any suggestion here?

                   

                  Bringing it back to my tables, my customers fields are in two separate tables and I would like to join them in order to only have to select one field instead of two fields. My efforts so far give me the problems I earlier described. I've made a simple illustration hereunder.

                   

                  How I want it to be:

                   

                  http://nl.tinypic.com/r/mk87bs/8

                   

                  When I select a customer in C (or customer group in D) I want to get the related values in A and B (both table 2 and 3).

                   

                  How it is now:

                   

                  http://nl.tinypic.com/r/eb7l2u/8

                   

                  Now when I select a customer in C (or customer group in D) I get the related values in A (table 2). If I also want the related values in B (table 3) I also have to select the same customer in E (or customer group in F).

                   

                  So in essential it is all about having to select only 1 field for customer or customer group instead of 2 fields. I don't want multiple customer field names with (sometimes) identical data values.

                   

                  Thank you in advance!