3 Replies Latest reply: Jan 26, 2013 2:25 PM by Gysbert Wassenaar RSS

    Get information from two tables into one table

      Can somebody help me with below situation?



      Table 'Fact_Sales' contains salesrecords with among others the colums 'Customer_number' and 'Ship_to_address_number'.



      Customer_number         Ship_to_address_number

      502999                                             1

      502999                                             5

      502999                                             0

      502999                                             0


      A number greater than 0 in column 'Ship_to_address_number' means that the billing of the sales went to the address of the customer and the shipping went to a different address. 


      Now I want to see in a new table the shippingdetails per salesrecord.

      When the column 'Ship_to_address_number' is greater than 0, Qlikview has to get his addressdetails from table 'Ship_to_address' with the combination of keys 'Customers_number' and 'Ship_to_address_number'.


      When the column 'Ship_to_address_number' is 0, Qlikview hast to get his addressdetails from table 'Customers' with the key Customer_number.


      How do I realise this?

        • Re: Get information from two tables into one table
          Gysbert Wassenaar

          Concatenate the addresses from the two tables into one address table and create a key in that table and in the Fact_Sales table to correctly associate the two tables. Something like this:



          load *,

          if(Ship_to_address_number = 0, Customer_number, Customer_number & '-' & Ship_to_address_number) as AddressKey

          from ...fact_sales_table...;



          load Customer_number as AddressKey, ...address fields...

          from ...customers_table...;



          load Customer_number & '-' & Ship_to_address_number as AddressKey, ...address fields...

          from ...ship_to_address_table...;

            • Re: Get information from two tables into one table

              Hi Gysbert,


              Thank you very much for the correct answer! Great to see that I've all the information in one table.


              Now I want to combine the colums of table Shippingdetails with each other. Is that also possible?


              Table ShippingDetails

              CustomerName (from Customers_table)     ShippingName (from ship_to_address_table)

              Laire Rivees                                                     -

                  -                                                                    Lives de Raves

                  -                                                                    Harrouts

              Laire Rivees                                                    -



              Desired result

              ShippingName (from Customers_table or Ship_to_address_table)

              Laire Rivees

              Lives de Raves


              Laire Rivees


              Kind regards,