topic Re: QVD Load with where clause in QlikView

QVD Load with where clause

Anonymous — Tue, 08 Sep 2015 12:59:30 GMT

I have a QVD with Standard Costs.

Item, StdCost, FromDate, ToDate

I am loading the Sales transactions with SQL from a a table.

Item, AccDate, Customer, OrderNo, Qty, SalesPrice

How do I join/load the StandardCost QVD where the AccDate is between the FromDate and Todate?

Re: QVD Load with where clause

petter — Tue, 08 Sep 2015 13:09:57 GMT

You should take advantage of the powerful IntervalMatch() function. Let the Standard Costs QVD be a Mapping table and use IntervalMatch().

Re: QVD Load with where clause

Anonymous — Tue, 08 Sep 2015 13:28:50 GMT

Petter,

An absolute novice with Qlikview so excuse any 'stupid' questions. My understanding is that a Mapping table can only have 2 columns. How would I load the Standard Costs QVD as a mapping table?

Re: QVD Load with where clause

jonathandienst — Tue, 08 Sep 2015 13:40:03 GMT

Peter is referring to a script like this:

// Load intervals from QVD

Intervals:

LOAD Item, StdCost, FromDate, ToDate

FROM StandardCosts.qvd (qvd);

//Load your transaction data / fact table

Sales:

LOAD Item,

AccDate,

Customer,

OrderNo,

Qty,

SalesPrice

;

SQL SELECT *

FROM MyTable;

// Match the AccDate to the intervals

Left JOIN (Sales)

IntervalMatch(AccDate, Item)

LOAD FromDate, ToDate, Item

Resident Intervals;

// optional BEGIN - This brings the StdCost into the main table

Left JOIN (Sales)

LOAD *

Resident Intervals;

DROP Table Intervals;

// optional END

Re: QVD Load with where clause

malini_qlikview — Tue, 08 Sep 2015 13:44:18 GMT

Hi Lolla,

Applymap is different from IntervalMatch, In your scenario you would need to match Accdate between the interval value FromDate and ToDate. Try the below

Data:

Load

Item, AccDate, Customer, OrderNo, Qty, SalesPrice

From Data_Table;

Intervals:

Load

Item, StdCost, FromDate, ToDate

From Stdcost_Interval_Table;

BridgeTable:

Left Join IntervalMatch (AccDate, Item)

Load Distinct FromDate,ToDate, Item Resident Intervals;

Re: QVD Load with where clause

Anonymous — Tue, 08 Sep 2015 13:49:47 GMT

Thank you!

Re: QVD Load with where clause

petter — Tue, 08 Sep 2015 13:53:06 GMT

I am sorry that I mislead you. You are absolutely right and there is no need for a mapping load with IntervalMatch()...

Re: QVD Load with where clause

Anonymous — Wed, 09 Sep 2015 08:09:40 GMT

Malini, how do I save the StdCost in the Sales QVD? Need this for another dashboard as well.