Load from 2 qvds

[Anonymous]

I have two QVDs, one with transactional data and one with costing data -

TranQVD:

Division,

InvoiceDate,

Item,

Qty,

..

CostQVD:

Division,

Item,

CostDate,

Cost,

Want to load into one QVD

NewQVD:

Division,

InvoiceDate,

Item,

Qty,

Cost,

( Qty*Cost) as CoS

The CostQVD contains the history of all the cost changes and the field CostDate is the date from which the cost is/was valid. The Cost from  CostQVD  needs to be the cost where the InvoiceDate is less or equal to the closest CostDate.

[Gysbert_Wassenaar]

Something like this maybe:

Temp:
LOAD
Division,
InvoiceDate,
Item,
Qty,
..
FROM Transactions.qvd (qvd);

join(Temp)

LOAD

Division,
Item,
CostDate,
Cost,
..
FROM Cost.qvd (qvd);

Result:
LOAD *, Qty*Cost as CoS
RESIDENT Temp
WHERE InvoiceDate <= CostDate
;

DROP TABLE Temp;

[marcus_malinow · Accepted solution]

CostsTmp:

LOAD

Division,

Item,

Floor(CostDate) as CostFromDateNum,

Cost

FROM

Costs.QVD (qvd)

Costs:

NOCONCATENATE LOAD

Division,

Item,

Divison & '_' & Item as DivisionItemKey,

CostFromDateNum,

If(Division = peek('Division', -1) and Item = peek('Item', -1),

    peek('CostFromDate', -1) - 1,

    num(makedate(2099,1,1)) as CostToDateNum,

Cost

RESIDENT CostsTmp

ORDER BY Division, Item, CostFromDate desc;

DROP TABLE CostsTmp

TranQVD:

LOAD

    Division,

    Item,

    Division & '_' & Item as DivisionItemKey,

    Qty,

    InvoiceDate,

    num(invoiceDate) as InvoiceDateNum

FROM Tran.qvd (qvd);

LEFT JOIN (TranQVD)

Intervalmatch (InvoiceDateNum, DivisionItemKey)

LOAD

    CostFromDateNum, CostToDateNum, DivisionItemKey

RESIDENT Costs;

LEFT JOIN (TranQVD)

LOAD

    CostFromDateNum,

    CostToDateNum,

    DivisionItemKey,

    Cost

RESIDENT Costs;

DROP TABLE Costs;

LEFT JOIN (TranQVD)

LOAD

    *,

    (Qty * Cost) as CoS

RESIDENT TranQVD;