Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

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?

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Get information from two tables into one table

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


talk is cheap, supply exceeds demand
3 Replies
MVP & Luminary
MVP & Luminary

Re: Get information from two tables into one table

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


talk is cheap, supply exceeds demand
Not applicable

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

MVP & Luminary
MVP & Luminary

Re: Get information from two tables into one table

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.


talk is cheap, supply exceeds demand