2 Replies Latest reply: Nov 2, 2011 5:55 PM by gregg111 RSS

    Create new table

      Hi

       

      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

          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

            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.