0 Replies Latest reply: Feb 6, 2018 7:18 PM by Ryan Giebel RSS

    Conditional Join in Qlik Sense

    Ryan Giebel

      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'