topic Re: least 13 in QlikView

least 13

vignesh_s — Wed, 25 Nov 2020 16:16:04 GMT

hi all

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

Re: least 13

sunny_talwar — Fri, 02 Feb 2018 12:38:01 GMT

May be like this

LOAD ...,

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

Resident/From ....

Group By ....;

Re: least 13

vignesh_s — Tue, 06 Feb 2018 07:46:00 GMT

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

Re: least 13

tresB — Tue, 06 Feb 2018 08:58:05 GMT

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);

Re: least 13

vignesh_s — Tue, 06 Feb 2018 09:45:57 GMT

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

Re: least 13

tresB — Tue, 06 Feb 2018 11:35:15 GMT

Could you post sample data and expected output against that?

Re: least 13

sunny_talwar — Tue, 06 Feb 2018 12:17:36 GMT

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

Re: least 13

vignesh_s — Tue, 06 Feb 2018 14:12:31 GMT

input:

table1:

load

key

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;

Re: least 13

vignesh_s — Tue, 06 Feb 2018 14:13:38 GMT

input:

table1:

load

key

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;

Re: least 13

tresB — Wed, 07 Feb 2018 06:04:39 GMT

You can still utilized the script I shared above like:

t1:

load key Inline [

key

];

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);