Force number format of rank function

[#1]

Hi all,

in a rather complex combination of a QV source application, a QV staging application, user input on SharePoint and nPrinting I use(d) the rank function to create a unique id for a record. That worked fantastic for the last months but this month, two records shared the same rank.

I obviously used the format option "Lowest rank on first row, then incremented by one for each row" for the rank function but due to the fact that I reference the rank column in the ID column (=[Version]&[Month]&[Rank]), QlikView uses the text format of the rank function. This text format however, can't be switched over to use the same logic as the number format (whatever the programers thought, when they implemented that ).

F1	Rank as Number	Rank as Text	ID
Val4	1	1	Version1-01-1
Val3	2	2	Version1-01-2
Val1	3	3	Version1-01-3
Val2	4	4-5	Version1-01-4
Val5	5	4-5	Version1-01-4

I tried a lot of approaches which I gathered from the community. Using any sort of RowNo() involvement doesn't work since it destroys the sorting (numerous articles on that behaviour here and also described in the manual). This also then prohibits me from using a simple rangesum across a dummy column with the value 1.

Next idea was to use the full accumulation across another dummy column with the value 1. This again works perfectly on the screen, but as soon as I reference to that dummy column, the concatenated string reads "Version1-01-1" for all records.

I tried to find information if there is a function similar to Num() that would force QlikView to use the numeric value in the contatenation of the ID but wasn't successfull with that.

So, if anyone has an idea on how to solve this rather simple thing, I would highly appreciate your input.

Thanks,

Henning

[marcus_sommer]

It's not quite clear for me what do you are trying how to achieve - the ranking itself is working and only the ranking-output is in a text-format or is there any other issue? Ranking could be quite complex within qlikview and maybe this posting is helpful for you: Continuous Ranking (no missing Rank).

- Marcus

[swuehl · Accepted solution]

What about

=[Version]&[Month]& Num([Rank])

[danielact]

Maybe try using an Above function? If(Rank=Above(Rank),Rank+1,Rank)

Note that I'm using Rank as the field name, not the function here

[#5]

I am almost embarassed. The solution is obviously Num([Rank])... I was so busy finding a complex solution, that I didn't try the obvious... Thank you all!