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'.

       

      Fact_Sales

      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:

           

          Fact_Sales:

          load *,

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

          from ...fact_sales_table...;

           

          Address_Table:

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

          from ...customers_table...;

           

          concatenate(Address_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

              Harrouts

              Laire Rivees

               

              Kind regards,

               

              Carol