QVD Load with where clause

[Anonymous]

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?

[petter]

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

[Anonymous]

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?

[jonathandienst · Accepted solution]

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

[malini_qlikview]

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;

[Anonymous]

Thank you!

[petter]

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

[Anonymous]

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