Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
lolliedd
Contributor
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
jonathandienst

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

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

View solution in original post

7 Replies
petter
MVP
MVP

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

lolliedd
Contributor
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?

jonathandienst

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

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

View solution in original post

malini_qlikview
Creator II
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:

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;


lolliedd
Contributor
Contributor
Author

Thank you!

petter
MVP
MVP

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

lolliedd
Contributor
Contributor
Author

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