1 Reply Latest reply: Feb 19, 2015 9:39 AM by Peter Cammaert RSS

    How to combine two QVD

    Aytac Bas

      I have two QVD (Product_Price_History and Order_Product) Can I make a new QVD from the data in these two QVD, as in SQL

       

      SQL

      select op.*,(select price_purchase from `s_product_price_history` ph where op.`product_id`=ph.`product_id` and ph.`date_added`<op.`insert_date` order by ph.`date_added` desc limit 1)  as price_purchase

       

        from `s_order_product` op

       

      where op.`insert_date` >='2015-02-18 23:59:59'

        • Re: How to combine two QVD
          Peter Cammaert

          Yes.

           

          NewQVD:

          LOAD * FROM S_ORDER_PRODUCT.QVD (QVD)

          WHERE INSERT_DATE >= '18/02/2015 23:59:59'; // Why load rows created in the last second of the previous day?

           

          MapPID2CD:

          MAPPING LOAD DISTINCT PRODUCT_ID, INSERT_DATE

          RESIDENT NewQVD;

           

          History:

          LOAD * FROM S_PRODUCT_PRICE_HISTORY.QVD (QVD); // No GROUP BY on external tables...

           

          LEFT JOIN (NewQVD)

          LOAD PRODUCT_ID,

               FirstSortedValue(PRICE_PURCHASE, -DATE_ADDED) AS PRICE_PURCHASE

          RESIDENT History

          WHERE DATE_ADDED < applymap('MapPID2CD', PRODUCT_ID)

          GROUP BY PRODUCT_ID;


          STORE NewQVD INTO NewQVD.QVD (qvd);


          I'm assuming that you already took care of converting SQL dates into QlikView dates before creating the original QVD's.