Rank number or instance number in QlikView Script

[Anonymous]

I'm looking for some help on optimizing my QV script in a particular scenario. What my current script does is find the rank (like SQL rank()), or the instance, of a particular widget in a table. For example:

I have widgets 1, 2 and 3.

Each widget performs a function.

Each time a widget performs its function, the data is recorded in a table.

What I need to find is the instance number each time a widget performs its function

RowNo Widget.ID Widget.FuncNo

1 1 1

2 2 1

3 1 2

4 3 1

5 1 3

6 3 2

The attached QVW illustrates how I currently accomplish this. I'm wondering if there is a better way to do it, as in reality it is performed on millions of rows.

Thanks in advance for any and all assistance.

-Jeff

[fernandotoledo · Accepted solution]

InterRecord script function does the job! try this:

Widgets:
LOAD * INLINE [
 WidgetTest.WidgetID, WidgetTest.RecNo
 1, 1
 2, 2
 5, 3
 7, 4
 2, 5
 3, 6
 4, 7
 2, 8
 1, 9
 1, 10
 7, 11
 5, 12
 6, 13
 3, 14
 6, 15
 4, 16
 5, 17
 1, 18
 7, 19
];
Widgets.Solution:
LOAD
 *,
 if(
 [WidgetTest.WidgetID] <> previous([WidgetTest.WidgetID]),
 1,
 (1+peek("Rank"))
 ) as Rank
resident Widgets
order by
 [WidgetTest.WidgetID],
 [WidgetTest.RecNo]
;
drop table Widgets;

best regards,

Fernando D'Agosto

[hector]

Hi, i did my own version, i don't know if it's better, but i did it in one step

check the attached file

rgds

[hector]

LOL, we did the same thing!! xD

[Anonymous]

Thank you so much! That worked great! Loaded in about 1/6th of the time!

Thanks again!

-Jeff