Firstly, I'm new to Qlikview so assume I know less than nothing. I have a report that has 2 resident tables from which I need to create a third table. The 2 tables are Quota and Assignment and a Quota can have multiple purchase or sale assignments that are joined by a FulfilmentID. So what I need to do is create a third table that matches up the purchase and sale assignments. The SQL to create the third table is (this could be done in other ways with maybe a self join to REPORT_ASSIGNMENT but let's ignore that for the time being):
PURCHASE_ASSIGNMENT_ID = MAX(CASE WHEN Q.PURCHASE_OR_SALE = 'P' THEN A.ASSIGNMENT_ID ELSE NULL END),
SALE_ASSIGNMENT_ID = MAX(CASE WHEN Q.PURCHASE_OR_SALE = 'S' THEN A.ASSIGNMENT_ID ELSE NULL END)
FROM REPORT_QUOTA Q
INNER JOIN REPORT_ASSIGNMENT A ON Q.QUOTA_ID = A.QUOTA_ID
WHERE A.FULFILMENT_ID IS NOT NULL
GROUP BY A.FULFILMENT_ID
ORDER BY A.FULFILMENT_ID
My problem is I don't know how to create the third table within Qlikview once REPORT_QUOTA and REPORT_ASSIGNMENT have been loaded.
Thanks for your help Kiran. It seems a third table is not necessary as Qlikview does much more behind the scenes than I thought. If I create a chart with a Dimension of FulfilmentID I can get the other info I need using expressions to filter on Purchase_or_Sale.