Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Load Command,Quantity,AutoNumber(RecNo(),Command) Inline [
Command,Quantity
A,3
A,2
A,3
B,4
B,3
B,2
B,6 ]
Hi,
Check this
http://community.qlik.com/blogs/qlikviewdesignblog/2014/08/29/recno-or-rowno
regards
ASHFAQ
Load Command,Quantity,AutoNumber(RecNo(),Command) Inline [
Command,Quantity
A,3
A,2
A,3
B,4
B,3
B,2
B,6 ]
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 ];
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;
AutoNumber(RecNo(),Command)
//the inline statement with variables
group by Command
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;
Thank you Anbu..
It works for me..