Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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;
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;