Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Load records repeatedly

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

Tags (3)
1 Solution

Accepted Solutions

Load records repeatedly

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

2 Replies

Load records repeatedly

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

Not applicable

Load records repeatedly

Thanks Rob

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

Lars

Community Browser