Get information from two tables into one table

[#1]

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?

[Gysbert_Wassenaar · Accepted solution]

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

[#3]

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

[Gysbert_Wassenaar]

Should be no problem. In the load from the customers table add a line CustomerName as ShippingName so the CustomerName is added as the field ShippingName. Since that field already exists in the Ship_to_address_table you won't need to add it there.