Counters in the Load

[hic]

Often when you create scripts, you need to create new fields, based on counters. There are several ways to do this. Some are simple, others not so…

Using RecNo()

The RecNo() function simply counts the input records and returns the number of the current record. Simple, and very useful if you want to create a record ID. However, if you concatenate several input files, or use a While clause, the numbers will not be unique.

Using RowNo()

The RowNo() function is very similar to the RecNo(), but this instead counts the output records and returns the number of the current record. Also simple and useful, especially if you concatenate several input tables. In such a case, the function will return consecutive numbers for the output table.

Using AutoNumber()

The AutoNumber() function is useful if you want to put a number on a specific field value, or on an attribute that is a combination of field values. The attributes will be numbered and their numbers re-used appropriately.

AutoNumber( Product & '|' & Date ) as ID

Using inline Peek() and RangeSum()

This is the mother of all record counters. Anything can be numbered; it can be done conditionally and anything can be used as condition. The idea is to fetch the counter value from the previous record and increase it only if some condition is fulfilled. Or reset it.

An example: For production quality control, some process indicator is measured and the Quality Manager wants to track trends and trend shifts. Then it is important to see the number of consecutive days that this indicator has increased or decreased. It is also good if the series of consecutive days can get a unique ID that can be used for selections and charts.

The following script creates these two fields; TrendID and DaysWithTrend.

Load *,
   If(  Change * Peek( Change ) > 0,
        Peek( TrendID ),
        RangeSum( 1, Peek( TrendID ))
        ) as TrendID,
   If(  Change * Peek( Change ) > 0,
        RangeSum( 1, Peek( DaysWithTrend )),
        0
        ) as DaysWithTrend;
Load
   Indicator,
   Indicator - Peek( Indicator ) as Change
Resident Daily_TQM_Measurement
   Order By Date;

First of all, the change of the indicator value is calculated in the bottom Load using the Peek() function. In the preceding Load, the change is used as condition in the If() function. The condition compares current record with the previous record using the last change and the second last change. If the product of the two is greater than zero, the trend has been the same two days in a row, so the TrendID of the previous record is used (the Peek() function) and DaysWithTrend is increased by one.

But if the condition is false, the TrendID is increased by one and the DaysWithTrend is reset to zero.

When a counter is increased, normal addition cannot be used since the Peek() will return NULL for the very first record. Instead the addition is made using the RangeSum() function.

Summary: You can create any auto-incrementing counter in a QlikView script. Just choose your counter function…

HIC