Qlik Community

Qlik Design Blog

All about product and Qlik solutions: scripting, data modeling, visual design, extensions, best practices, etc.

Employee
Employee

Counters in the Load

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.

Quality indicator.png

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

Load *,

   IfChange * Peek( Change ) > 0,

        Peek( TrendID ),

        RangeSum( 1, Peek( TrendID ))

        ) as TrendID,

   IfChange * 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. 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

12 Comments

Nice one as always...

181 Views
carbal1952
Contributor II

I took note (one more time).

Thnaks a lot (one more time).

0 Likes
181 Views
Not applicable

Looks geat do you have the example in qvw please

181 Views
Employee
Employee

I used the above script on top of randomly generated data:

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 *,

  Week(Date) as WeekNo,

  Indicator - Peek(Indicator) as Change;

// Generation of the field Indicator vs Date

Load 0.5+RangeSum(0.6*Peek(Indicator),0.1*Rand(),0.3*Peek(Change)) as Indicator ,

  Date(MakeDate(2013)-1+RecNo()) as Date

  Autogenerate 100;

HIC

0 Likes
181 Views
dvqlikview
Honored Contributor II

Good post. Thanks HIC.

181 Views
Not applicable

could you give more exaples on auto increment numbers? maybe an attachment?

0 Likes
181 Views
Not applicable

to optimize the loading of my fields (contains an ID attribute is unique), it is preferable to use the ID field or I create it with RowNo().

Thanks

0 Likes
181 Views
Not applicable

Please check out the Generic keys

Thanks,

Sri

0 Likes
181 Views
psankepalli
Contributor III

Good post. Thanks HIC.

0 Likes
181 Views
Arjunarao
Honored Contributor II

Very nice article HIC. Thank you.

181 Views
Not applicable

very Nice article

0 Likes
181 Views
balkumarchandel
Valued Contributor II

As usual very good post . Thanks HIC

0 Likes
181 Views