Create an ascending Key Calculation

[#1]

Hi Community,

I have a requirement to have a Qlikview Report with these structure:

RecordKey	OrderNum	LineNum	VAT
1	123	0	X1
1	123	1	X1
2	124	0	X0
2	124	1	X1
2	124	2	X2
3	125	0	X01
3	125	1	X1

Basically, the column OrderNum, LineNum and VAT are cointained in the source data and I would need to create RecordKey column through a calculation (because I want the user to select the ordernums). Recordkey is a number that starts in 1 for the first OrderNum and increases for each ordernum but not each line.

I have tried to accomplish that by using the rank function in this way but with no good result:

=rank(ONLY(OrderNum),4,1)

Can anybody help me with this please?

Thanks

Julian

[Gysbert_Wassenaar]

Try aggr(rowno(),OrderNum)

[#3]

Hi Gysbert,

With your formula it only shows one LineNum per OrderNum, i.e. something like this

RecordKey	OrderNum	LineNum	VAT
1	123	0	X1
2	124	0	X0
3	125	0	X01

But I would like to show all lines per OrderNum:

RecordKey	OrderNum	LineNum	VAT
1	123	0	X1
1	123	1	X1
2	124	0	X0
2	124	1	X1
2	124	2	X2
3	125	0	X01
3	125	1	X1

[alexandros17]

Try This Expression:

rank(TOTAL -OrderNum,  1,1)

Hope it helps

[Gysbert_Wassenaar · Accepted solution]

If you use it as an expression you need to add the nodistinct keyword: aggr(nodistinct rowno(),OrderNum)

[#6]

Yes I was about to reply that. This works

Thanks

Julian

[#7]

try with autonumber function

AutoNumber(OrderNum)