# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
cancel
Showing results for
Did you mean:
Contributor

## QVD Load with where clause

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?

1 Solution

Accepted Solutions

Peter is referring to a script like this:

Intervals:

FROM StandardCosts.qvd (qvd);

Sales:

AccDate,

Customer,

OrderNo,

Qty,

SalesPrice

;

SQL SELECT *

FROM MyTable;

// Match the AccDate to the intervals

Left JOIN (Sales)

IntervalMatch(AccDate, Item)

Resident Intervals;

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

Left JOIN (Sales)

Resident Intervals;

DROP Table Intervals;

// optional END

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
7 Replies
MVP

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

Contributor
Author

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?

Peter is referring to a script like this:

Intervals:

FROM StandardCosts.qvd (qvd);

Sales:

AccDate,

Customer,

OrderNo,

Qty,

SalesPrice

;

SQL SELECT *

FROM MyTable;

// Match the AccDate to the intervals

Left JOIN (Sales)

IntervalMatch(AccDate, Item)

Resident Intervals;

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

Left JOIN (Sales)

Resident Intervals;

DROP Table Intervals;

// optional END

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Creator II

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:

Item, AccDate, Customer, OrderNo, Qty, SalesPrice

From Data_Table;

Intervals:

Item, StdCost, FromDate, ToDate

From Stdcost_Interval_Table;

BridgeTable:

Left Join IntervalMatch (AccDate, Item)

Load Distinct FromDate,ToDate, Item Resident Intervals;

Contributor
Author

Thank you!

MVP

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

Contributor
Author

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