Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I need to load only rows which fall into the top 4 values in a field
Risks:
RiskID | Score |
1234 | 50 |
1235 | 24 |
1236 | 45 |
1237 | 45 |
1238 | 27 |
1239 | 32 |
1240 | 35 |
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
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:
In your case, you should change First 2 to First 4.
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
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;
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: