2 Replies Latest reply: Jun 9, 2017 10:06 AM by Michele De Nardi

# RowNo() Script wrong result

Hi,

have a look on my script:

Abschriften:

[

Markt,Artikel,Abschrift_1,Abschrift_2

1,Buch, -1.10, -5.25

1,Brot, -2.50, -7.75

1,Wasser, -0.95, -0.70

1,Apfel, -1.10, -0.80

1,Salat, -4.50, -2.25

2,Tee, -7.00, -4.50

2,Kaffee, -1.25, -3.25

2,Birne, -3.50, -1.25

2,Deo, -1.75, 0.85

];

Tmp_Markt:

NoConcatenate

Markt as Tmp_Markt_Field

Resident Abschriften;

Final:

NoConcatenate

[

ID,

0

];

Set a=0;

Do while a<=1

LET vMarkt = peek('Tmp_Markt_Field', \$(a), 'Tmp_Markt');

Concatenate(Final)

Markt as TestMarkt

, Artikel as TestArtikel

, RowNo()-1 as Rank

Resident Abschriften where Markt = \$(vMarkt) order by Abschrift_1;

Let a=a+1;

Loop

drop field ID;

drop table Tmp_Markt;

I want to have a rank based on the KPI Abschrift1 per market. But qlik sense gives me back a rank on total. But I don't know why? First I rank the artikel for makrte 1 and make a cocanenation on Final and then he takes the market 2 and makes a cocncatenoin on Final. But the result is in total for Rank 1,2,3,4,5,6,7,8,9,10 but I need thik like 1,2,3... for market1 and 1,2,3... for market two.

Where is the mistake?

• ###### Re: RowNo() Script wrong result

That's by design, Rowno() will consider all rows of the concatenated tables.

Try

Abschriften:

[

Markt,Artikel,Abschrift_1,Abschrift_2

1,Buch, -1.10, -5.25

1,Brot, -2.50, -7.75

1,Wasser, -0.95, -0.70

1,Apfel, -1.10, -0.80

1,Salat, -4.50, -2.25

2,Tee, -7.00, -4.50

2,Kaffee, -1.25, -3.25

2,Birne, -3.50, -1.25

2,Deo, -1.75, 0.85

];

NoConcatenate

LOAD *, AutoNumber(RecNo(), Markt) as Rank

Resident Abschriften

Order by Markt, Abschrift_1;

DROP TABLE Abschriften;

• ###### Re: RowNo() Script wrong result

Try this solution:

Abschriften:

[

Markt,Artikel,Abschrift_1,Abschrift_2

1,Buch, -1.10, -5.25

1,Brot, -2.50, -7.75

1,Wasser, -0.95, -0.70

1,Apfel, -1.10, -0.80

1,Salat, -4.50, -2.25

2,Tee, -7.00, -4.50

2,Kaffee, -1.25, -3.25

2,Birne, -3.50, -1.25

2,Deo, -1.75, 0.85

];

Tmp_Markt:

NoConcatenate

Markt as Tmp_Markt_Field

Resident Abschriften;

let a=0;

For i=0 to NoOfRows('Tmp_Markt')-1;

let vMarkt=Peek('Tmp_Markt_Field',i,'Tmp_Markt');

Final:

Markt as TestMarkt,

Artikel as TestArtikel,

rowno()-\$(a) as Rank

Resident Abschriften where Markt=\$(vMarkt);

let a=NoOfRows('Final');

next

drop Table Tmp_Markt;