How to combine two QVD

[aytacbas]

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'

[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.