topic Re: Load from 2 qvds in QlikView

Load from 2 qvds

Anonymous — Mon, 20 Apr 2015 15:37:31 GMT

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.

Re: Load from 2 qvds

Gysbert_Wassenaar — Mon, 20 Apr 2015 15:41:37 GMT

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;

Re: Load from 2 qvds

marcus_malinow — Mon, 20 Apr 2015 16:19:13 GMT

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;