Rank by dimension - need positions

[reporting_neu]

Hi,

I have several dimensions and no Expression. I need to assign position numbers for each different dimension, but I don't know how. 

This is what the data looks like:

BNumber	RMNo
5080000	101
5080000	102
5080000	106
5080040	367
5080040	375

This is what it should look like:

BNumber	RMNo	Pos
5080000	101	1
5080000	102	2
5080000	106	3
5080040	367	1
5080040	375	2

Can you help me please?

[Clement15]

Hello,

This should meet your need:

TEST:
load *
Inline [
BNumber, RMNo
5080000 ,101
5080000 ,102
5080000 ,106
5080040 ,367
5080040 ,375
];

NoConcatenate

TEST2:
load 
*,
IF(ROWNO()=1,1,if(BNumber=Previous(BNumber),PEEK('Rank',-1)+1,1)) AS Rank
Resident TEST
Order By BNumber,RMNo;

Drop table TEST;

[sbaro_bd]

Hello @reporting_neu ,

If you don't want to make change son your script, you can use the Rank() function with some constraints (take a look on the attached file).

I only recommand this solution if you don't plan to use the "Pos" dimension in others calculations. Otherwise, @Clement15 is the best way.

Regards.

[reporting_neu]

Vielen Dank für Ihre Arbeit und Unterstützung.

@Clement15 

Ich habe die Funktion „RowNo“ im Skript ausprobiert. Allerdings bekomme ich beim ersten Eintrag nur eine 1 und danach Null-Werte.

@sbaro_bd 

Ich weiß nicht, ob es an der Anzahl der Dimensionen liegt. Wenn ich nur die beiden Dimensionen "BNumber" und "RMNo" verwende, funktioniert die Funktion "Rank".

Wenn ich es jedoch in meiner Tabelle verwende, die viele Dimensionen hat, erhalte ich überall eine 1.

Für meine Zwecke würde die Rank-Funktion ausreichen.

[Clement15]

Hello,

Can you share your script with me?

[sbaro_bd]

Hello @reporting_neu ,

I see this on the community : https://community.qlik.com/t5/QlikView-App-Dev/Rank-within-a-specific-dimension-when-you-have-multip...

So, try the expression like this and let us know if it works :

AGGR(Rank(RMNo),BNumber,RMNo)

Regards.

[reporting_neu]

@Clement15

Should be your script exactly:

G1:
Load
*,
IF(ROWNO()=1,1,if(BNumber=Previous(BNumber),PEEK('Rank',-1)+1,1)) AS Rank
Resident TMP_G1
Order By BNumber,RMNo;

Drop Table TMP_G1;

@sbaro_bd 

That looks better.

I also have different parts in the respective rows. Can I insert the data field "Parts" somewhere else? Qlik now distinguishes between BNumber and RMNo but not Part yet. If that works, I have everything I need.

[Qrishna]

Simple one line solution:

AutoNumber(BNumber&'-'&RMNo,BNumber) as Rank

Add this in your table script;

Data:
LOAD BNumber,
RMNo,
AutoNumber(BNumber&'-'&RMNo,BNumber) as Rank
FROM
[https://community.qlik.com/t5/New-to-Qlik-Analytics/Rank-by-dimension-need-positions/td-p/2498915]
(html, codepage is 1252, embedded labels, table is @1);

[reporting_neu · Accepted solution]

Thank you for your answer. Unfortunately the same result as before.

Qlik does differentiate BNumber and RMNo but if there are additional different parts (post before), they will still receive the same rank.

But if find the sulution in expression:

=AGGR(Rank(BNumber &'-'& RMNo &'-'& Part),BNumber, Part)