How to combine two QVD

aytacbas — Thu, 19 Feb 2015 14:08:28 GMT

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 — Thu, 19 Feb 2015 14:37:12 GMT

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.

topic How to combine two QVD in QlikView

How to combine two QVD

Re: How to combine two QVD