topic Re: Create an ascending Key Calculation in QlikView

Create an ascending Key Calculation

Tue, 25 Jun 2013 11:08:20 GMT

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

Re: Create an ascending Key Calculation

Gysbert_Wassenaar — Tue, 25 Jun 2013 11:31:49 GMT

Try aggr(rowno(),OrderNum)

Re: Create an ascending Key Calculation

Tue, 25 Jun 2013 11:38:24 GMT

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

Re: Create an ascending Key Calculation

alexandros17 — Tue, 25 Jun 2013 11:41:03 GMT

Try This Expression:

rank(TOTAL -OrderNum, 1,1)

Hope it helps

Re: Create an ascending Key Calculation

Gysbert_Wassenaar — Tue, 25 Jun 2013 11:47:58 GMT

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

Re: Create an ascending Key Calculation

Tue, 25 Jun 2013 11:49:03 GMT

Yes I was about to reply that. This works

Thanks

Julian

Re: Create an ascending Key Calculation

Tue, 25 Jun 2013 11:53:30 GMT

try with autonumber function

AutoNumber(OrderNum)