Conditional Join in Qlik Sense

ryan_giebel — Wed, 07 Feb 2018 00:18:24 GMT

I am trying to join two QVD tables based on a field in a third table (since the primary key in the first table will not have a match in the third table for products that are refunded). The field in the third table contains order_type field which is the base for my conditional join. Is there a way to have a conditional join in the load script to make this possible?

Tables:

1. order_products

2. order_product_inventory

3. orders

Below are the two conditions:

#1. if orders.order_type <> 'refund' join order_products.id to order_product_inventory.order_product_id

#2. if orders.order_type = 'refund' join order_products.refunded_order_product_id to order_product_inventory.order_product_id

If you'd like to see the sql query for the two conditions, see below:

#1 order_type <> 'refund'

Select *

FROM orders ord

LEFT JOIN order_products opr on ord.id = opr.order_id

LEFT JOIN order_product_inv opi on opr.id = opi.order_product_id

WHERE ord.order_type <> 'refund'

#2 order_type = 'refund'

Select *

FROM orders ord

LEFT JOIN order_products opr on ord.id = opr.order_id

LEFT JOIN order_product_inventory opi on opr.refunded_order_product_id = opi.order_product_id

WHERE ord.order_type = 'refund'

topic Conditional Join in Qlik Sense in App Development

Conditional Join in Qlik Sense