Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

least 13

hi all

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

1 Solution

Accepted Solutions
Highlighted
MVP
MVP

Re: least 13

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

View solution in original post

9 Replies
Highlighted

Re: least 13

May be like this

LOAD ...,

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

Resident/From ....

Group By ....;

Highlighted
Creator
Creator

Re: least 13

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

Highlighted
MVP
MVP

Re: least 13

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

Highlighted
Creator
Creator

Re: least 13

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

Highlighted
MVP
MVP

Re: least 13

Could you post sample data and expected output against that?

Highlighted

Re: least 13

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

Highlighted
Creator
Creator

Re: least 13

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;

Highlighted
Creator
Creator

Re: least 13

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;

Highlighted
MVP
MVP

Re: least 13

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

View solution in original post