similar to the SQL Function ROW_NUMBER() OVER Clause in QlikView?

[adhudson]

Hi,

I would like to know if QlikView has any function which is similar to the SQL Function ROW_NUMBER() OVER Clause?

Otherwise I would ask "How can I calculate the number of occurrences of a value in a field like below?"

In this below table, I want to calculate the Count column.

Count	Command	Quantity
1	A	3
2	A	2
3	A	3
1	B	4
2	B	3
3	B	2
4	B	6

Thanks,

Andrew Hudson.

[ashfaq_haseeb]

Hi,

Check this

http://community.qlik.com/blogs/qlikviewdesignblog/2014/08/29/recno-or-rowno

regards

ASHFAQ

[anbu1984 · Accepted solution]

Load Command,Quantity,AutoNumber(RecNo(),Command) Inline [

Command,Quantity

A,3

A,2

A,3

B,4

B,3

B,2

B,6 ]

[MarcoWedel]

Load Command,

        Quantity,

        AutoNumber(RecNo(),Command) as Count

Inline [

Command,Quantity

A,3

A,2

A,3

B,4

B,3

B,2

B,6 ];

[anbu1984]

If Command is not sorted, then try this

Initial:

Load * Inline [

Command,Quantity

A,3

A,2

A,3

B,4

B,3

B,2

B,6];

NoConcatenate

Final:

Load Command,Quantity,AutoNumber(RecNo(),Command) Resident Initial Order by Command;

Drop table Initial;

[#6]

AutoNumber(RecNo(),Command)

//the inline statement with variables

group by Command

[anbu1984]

Using Peek

Initial:

Load * Inline [

Command,Quantity

A,3

A,2

A,3

B,4

B,3

B,2

B,6];

NoConcatenate

Final:

Load Command,Quantity,If(Command <> Peek(Command),1,Peek(Count)+1) as Count Resident Initial Order by Command;

Drop table Initial;

[adhudson]

Thank you Anbu..

It works for me..