Load records repeatedly

[#1]

Hi QlikCommunity!

I am often struggling with repeatedly loading single records with the condition that the number of iterations should be the variance between a field currently read and the same field value in the next record.

Example:

The enclosed QlikView application shows a typical setup from our ERP system. This table contains information about cost price calculation(IDs). In this case 3 calculations have been made for item 166404 and 2 for 165000. The "transdate" = validation date and thus will hold until a new calculation is performed with a newer TransDate.

Sine I want to use this table as a link table to decide which particular PriceCaldID is valid for a certain ItemID at a certain production time (=TransDate), I figured that continuously loading each record with something like this:

Load ItemID,

PriceCalcID,

Transdate + Iterno() as Valid_date

While IterNo() <= Previous(transdate) - Transdate

Order by ItemID Desc,

PriceCalcID Desc,

Transdate Desc

Resident DATA

(since I only know how to read a value from a preceeding record I obviously suggest to load the data in Descending order)

Anyway - what I need is this result:

ITEMID	PRICECALCID	TRANSDATE	Reload add.
166404	1077	13-05-2009
166404	1077	14-05-2009	Added
166404	1080	15-05-2009
166404	1081	16-05-2009
165000	1090	29-05-2009
165000	1090	30-05-2009	Added
165000	1091	31-05-2009

I appreciate your feedback

Regards,

Lars Madsen

[rwunderlich · Accepted solution]

The approach I usually take is:

1. Process the rows in DESC data order to assign an end date to each start date.
2. Generate a calendar table from min to max date.
3. IntervalMatch to create rows for every date in every range.

For an example of this, see the QV Cookbook sample "Fill values in a data range using previous values".

-Rob

[#3]

Thanks Rob

It worked out beautifully in my live application as well - love the recipes of your Cookbook

Lars