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

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

adhudson — Mon, 22 Sep 2014 13:53:06 GMT

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.

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

ashfaq_haseeb — Mon, 22 Sep 2014 13:59:30 GMT

Hi,

Check this

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

regards

ASHFAQ

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

anbu1984 — Mon, 22 Sep 2014 14:04:09 GMT

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

Command,Quantity

A,3

A,2

A,3

B,4

B,3

B,2

B,6 ]

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

MarcoWedel — Mon, 22 Sep 2014 14:06:57 GMT

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 ];

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

anbu1984 — Mon, 22 Sep 2014 14:07:47 GMT

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;

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

Mon, 22 Sep 2014 14:10:57 GMT

AutoNumber(RecNo(),Command)

//the inline statement with variables

group by Command

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

anbu1984 — Mon, 22 Sep 2014 14:15:56 GMT

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;

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

adhudson — Mon, 22 Sep 2014 14:24:07 GMT

Thank you Anbu..

It works for me..