1 Reply Latest reply: Jul 11, 2013 4:10 AM by Kaushik Solanki RSS

    selection on two independent tables

    Mario Hölzer

      Hello,

       

      There are two tables in our Datamodel, which are not connected to each other, because there isnt a unique key in the database.

      This is the delivery table, which includes all supplies per customer per period.

      The other table is the complaint table where all the complaints are filed, but no clear attribution to the deliveries.

      To get the supplies to a complaint you must first filter the complaints (eg, month, year, customer) and then apply this filter on the delivery table.

      Now it may be that there are delivieries in a period for a customer, but no complaints, and vice versa.

      We need a view that displays all the complaints and deliveries per customer. So far we get only the correct data if you explicitly select a customer, but not when displaying a list of all customers (no filters selected).

      The Appendix contains a .qvw file which contains a simple example of this problem. In addition, an Excel file that contains the desired view of us.

       

      Regards

      knelt

        • Re: selection on two independent tables
          Kaushik Solanki

          Hi,

           

               Try this script.

           

          Complaint:
          LOAD ComplaintCustomer as Customer,ComplaintValue INLINE
          [ ComplaintCustomer, ComplaintValue
            
          A, 1
          A, 1
          B, 1
          B, 1
          C, 1
          ];

          Deliver:
          LOAD DeliverCustomer as Customer,DeliverValue INLINE
          [ DeliverCustomer, DeliverValue
            
          A, 20
          B, 30
          B, 10
          D, 45
          ];

           

           

          And then create a chart with dimension as Customer,

                                                        Expression as Sum(DeliverValue) and

                                                       Expression as Sum(ComplaintValue)

           

           

          Regards,

          Kaushik Solanki