RowNo() Script wrong result

[Anonymous]

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,

0

];

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?

[swuehl]

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;

[micheledenardi]

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');

next

drop Table Tmp_Markt;