Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
spikenaylor1
Creator
Creator

Script Load rows for to 4 Values in field

I need to load only rows which fall into the top 4 values in a field

Risks:

RiskIDScore
123450
123524
123645
123745
123827
123932
124035

 

So for the above table, I only need to load RiskID 1234, 1236, 1237, 1239, 1240 as the scores fall into the top 4 for the field

 

|Regards

 

Labels (1)
4 Replies
Thiago_Justen_

Hi there,

There are some others ways yo ucan do this, but I'll show you one.

 

Temp:
LOAD * inline [
Customer|Product|OrderNumber|UnitSales|CustomerID
Astrida|AA|1|10|1
Betacab|AA|5|4|2
Canutility|CC|13|19|3
Divadip|AA|9|16|4
] (delimiter is '|');

NoConcatenate

First 2
Final:
LOAD * Resident Temp Order By UnitSales desc;

Drop Table Temp;

 

Output:

image.png

 

In your case, you should change First 2 to First 4. 

Thiago Justen Teixeira Gonçalves
Farol BI
WhatsApp: 24 98152-1675
Skype: justen.thiago
spikenaylor1
Creator
Creator
Author

I have tried something similar to this

unfortunately

first 2 will only load the first two rows 

 

If my top 3 rows are

100

99

99

i need all three loaded as all three rows are in the top two values

first 2 will miss row 3 in this case

regards

 

Jflori4n
Contributor
Contributor

Hi Spike,

You can try with:

 

Temp:
LOAD * inline [
Customer|Product|OrderNumber|UnitSales|CustomerID
Astrida|AA|1|10|1
Betacab|AA|5|4|2
Canutility|CC|13|19|3
Divadip|AA|9|16|4
] (delimiter is '|');

NoConcatenate

//First 2
Final:
LOAD * ,
If(RowNo()=1,1,(1+peek("Rank"))) as Rank
Resident Temp
Order By UnitSales desc;

Drop Table Temp;

MayilVahanan

Hi 

Try like below

Temp:
LOAD * INLINE [
RiskID, Score
1234, 50
1235, 24
1236, 45
1237, 45
1238, 27
1239, 32
1240, 35
];

Temp1:
Load *, If(Rowno() = 1, 1, If(Peek('Score') <> Score, Peek('Rank')+1, Peek('Rank'))) as Rank Resident Temp Order by Score desc;

NoConcatenate
Final:
Load RiskID, Score Resident Temp1 where Rank <= 4;

DROP Table Temp, Temp1;

O/P:

MayilVahanan_0-1632453453412.png

 

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.