topic Re: Script Load rows for to 4 Values in field in App Development

Script Load rows for to 4 Values in field

spikenaylor1 — Tue, 30 Nov 2021 16:25:32 GMT

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

Re: Script Load rows for to 4 Values in field

Thiago_Justen_ — Thu, 23 Sep 2021 13:53:28 GMT

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.

Re: Script Load rows for to 4 Values in field

spikenaylor1 — Thu, 23 Sep 2021 21:35:47 GMT

I have tried something similar to this

unfortunately

first 2 will only load the first two rows

If my top 3 rows are

100

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

Re: Script Load rows for to 4 Values in field

Jflori4n — Fri, 24 Sep 2021 00:04:34 GMT

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;

Re: Script Load rows for to 4 Values in field

MayilVahanan — Fri, 24 Sep 2021 03:17:42 GMT

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: