topic Get information from two tables into one table in QlikView

Get information from two tables into one table

Tue, 22 Jan 2013 16:05:19 GMT

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

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 — Tue, 22 Jan 2013 18:38:26 GMT

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

Fri, 25 Jan 2013 08:30:38 GMT

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

Re: Get information from two tables into one table

Gysbert_Wassenaar — Sat, 26 Jan 2013 19:25:54 GMT

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.