Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Become an analytics expert with Qlik's new 15 week course: Applied Data Analytics using Qlik Sense. READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
adhudson
Creator II
Creator II

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

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.

CountCommandQuantity
1A3
2A2
3A3
1B4
2B3
3B2
4B6

Thanks,

Andrew Hudson.

1 Solution

Accepted Solutions
anbu1984
Master III
Master III

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

Command,Quantity

A,3

A,2

A,3

B,4

B,3

B,2

B,6 ]

View solution in original post

7 Replies
ashfaq_haseeb

anbu1984
Master III
Master III

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

Command,Quantity

A,3

A,2

A,3

B,4

B,3

B,2

B,6 ]

View solution in original post

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
Master III
Master III

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;

Not applicable

AutoNumber(RecNo(),Command)

//the inline statement with variables

group by Command

anbu1984
Master III
Master III

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
Creator II
Creator II
Author

Thank you Anbu..

It works for me..