topic Re: two associations between tables different filters by constant type in App Development

two associations between tables different filters by constant type

tgSimplay3 — Mon, 20 Dec 2021 21:38:40 GMT

How to create this association for ShipTo and BillTo address associated to an order header? In T-SQL for Microsoft SQL Server, we use the following SQL for reports.

select h.cust_order_number, BillTo.Name, BillTo.Email, ShipTo.Name, ShipTo.State, ShipTo.Country

from orders h

left outer join addresses ShipTo on h.shipAddressRef = ShipTo.addressID and ShipTo.type = 'SHIP_TO'

left outer join addresses BillTo on h.shipAddressRef = BillTo.addressID and ShipTo.type = 'BILL_TO'

The first association is created correctly, when I pick the address table and apply a filter (i.e. type = 'SHIP_TO'). How do I pick the same table and apply a different filter for the BILL_TO? Qlik Sense Data Manager is replacing the first address and association, instead of making the second association as I intend.

Re: two associations between tables different filters by constant type

jochem_zw — Sat, 22 Feb 2020 16:12:33 GMT

Why not create 2 dimension tables

1 for shipping address and 1 for billing address

based on your SQL they can have the same key

Re: two associations between tables different filters by constant type

tgSimplay3 — Mon, 09 Mar 2020 13:03:02 GMT

Jochem_zw suggested I create 2 dimension tables. It is exactly what I'm trying to do. But it seems impossible with the Data Manager and the "Auto-generated section" of the Data load editor. Do I need to "Unlock" the script and do something special? If yes, how to load/create 2 different tables from the same SQL data table ... each with its separate where clause. Can you provide an example?