least 13

[vignesh_s]

hi all

How could i find the sum of least 13 values in load script

[sunny_talwar]

May be like this

LOAD ...,

     RangeSum(Min(Value, 1), Min(Value, 2), Min(Value, 3)....., Min(Value, 13)) as SumOf13MinValue

Resident/From ....

Group By ....;

[vignesh_s]

hi sunny thanks,what is expression for to find least 50 r least 100,can v write dynamic expression ?

[tresesco]

If you are taking about single field values.. try something like:

t1:

Load

       Floor(RowNo()*Rand()*100) as Value

AutoGenerate 20 ;

NoConcatenate

t2:

Load Distinct

       Value

Resident t1 Order By Value desc;

Drop Table t1;

Let vMin13th=FieldValue('Value',13);    // change here your XXth value

Load

       Sum(Value) as SumOfLeast13Values

Resident t2 where Value<=$(vMin13th);

[vignesh_s]

thanks tresesco,but im nt using single field im using group function in it

[tresesco]

Could you post sample data and expected output against that?

[sunny_talwar]

I agree, it would be easier to help if we know what all fields you have and at what level are you trying to do your Min

[vignesh_s]

input:

table1:

load

key

A

B

C

D

F

G

FROM....

JOIN

load

key value

A 2

A 3

A 4

A 5

B 6

B 7

C 8

D 9

. .

. .

. .

. .

. .

from...........

like this i have 1000 of value

i wanna sum of least 50 r least 100 values , need expression dynamicaly in load script

output:

load *

sum of value

resident table1

group by key;

drop table table1;

[vignesh_s]

input:

table1:

load

key

A

B

C

D

F

G

FROM....

JOIN

load

key value

A 2

A 3

A 4

A 5

B 6

B 7

C 8

D 9

. .

. .

. .

. .

. .

from...........

like this i have 1000 of value

i wanna sum of least 50 r least 100 values , need expression dynamicaly in load script

output:

load *

sum of value

resident table1

group by key;

drop table table1;

[tresesco · Accepted solution]

You can still utilized the script I shared above like:

t1:

load key Inline [

key

A

B

C

D

F

G

];

JOIN

load * Inline [

key value

A 2

A 3

A 4

A 5

B 6

B 7

C 8

D 9](delimiter is spaces);

//t1:

//Load

// Floor(RowNo()*Rand()*100) as Value

//AutoGenerate 20 ;

NoConcatenate

t2:

Load Distinct

       value

Resident t1 Order By value desc;

Drop Table t1;

Let vMin13th=FieldValue('value',3);        //Change the number here for Xth value

Load

       Sum(value) as SumOfLeast13Values

Resident t2 where value<=$(vMin13th);