topic Create new table in QlikView

Create new table

Wed, 02 Nov 2011 04:24:20 GMT

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):

SELECT

A.FULFILMENT_ID,

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.

Any help is appreciated.

Thanks

Gregg.

Create new table

Wed, 02 Nov 2011 05:34:30 GMT

Hi Gregg,

The following would be syntax to create the 3rd table.

Load * Resident REPORT_QUOTA;

Join

Load * Resident REPORT_ASSIGNEMENT where not isNull(FULFILMENT_ID);

The join on QUOTA_ID is automatic as QlikVIew works on natural joins. Once you create the 3rd table drop the two tables if you dont need them, else syntehetic keys will be created.

Kiran.

Create new table

Wed, 02 Nov 2011 21:55:15 GMT

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.

Thanks

Gregg.