topic RowNo() Script wrong result in QlikView

RowNo() Script wrong result

Anonymous — Fri, 09 Jun 2017 13:30:46 GMT

Hi,

have a look on my script:

Abschriften:

Load * inline

[

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,Schokolade, -2.50, 0.45

2,Deo, -1.75, 0.85

];

Tmp_Markt:

NoConcatenate

LOAD Distinct

Markt as Tmp_Markt_Field

Resident Abschriften;

Final:

NoConcatenate

LOAD * Inline

[

ID,

];

Set a=0;

Do while a<=1

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

Concatenate(Final)

Load

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

swuehl — Fri, 09 Jun 2017 14:03:26 GMT

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

Try

Abschriften:

Load * inline

[

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,Schokolade, -2.50, 0.45

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

micheledenardi — Fri, 09 Jun 2017 14:06:29 GMT

Try this solution:

Abschriften:

Load * inline

[

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,Schokolade, -2.50, 0.45

2,Deo, -1.75, 0.85

];

Tmp_Markt:

NoConcatenate

LOAD Distinct

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:

Load

Markt as TestMarkt,

Artikel as TestArtikel,

rowno()-$(a) as Rank

Resident Abschriften where Markt=$(vMarkt);

let a=NoOfRows('Final');

drop Table Tmp_Markt;