topic Re: pick a value from table in QlikView

pick a value from table

Thu, 08 May 2014 13:23:27 GMT

Hi all,

there is a table and i want specific row number to be picked in an expression after its sorted by a Column

ID Qty

1 100

2 200

3 150

4 175

5 250

this table should be sorted by Qty ascending and i want to pick 3rd ranked Qty in expression value

which function i can use

thanks

Sukhwant

Re: pick a value from table

anbu1984 — Thu, 08 May 2014 13:28:51 GMT

Try Min(Qty,3)

Load Min(Qty,3) Inline [

ID ,Qty

1,100

2 ,200

3 ,150

4 ,175

5 ,250 ];

Re: pick a value from table

anbu1984 — Thu, 08 May 2014 13:32:37 GMT

Using FirstSortedValue

Load FirstSortedValue(Qty,Qty,3) Inline [

ID ,Qty

1,100

2 ,200

3 ,150

4 ,175

5 ,250 ];

Re: pick a value from table

Thu, 08 May 2014 13:42:07 GMT

need this in an expression and not in Script. this 3 i will later declare as a vairable to make it flexible.

Also the challenge is in some cases QTY value is same

please advise a expression solution

Re: pick a value from table

anbu1984 — Thu, 08 May 2014 13:47:46 GMT

You can use Min(Qty,3) in expression. This works even in case of duplicates

Update using variable

=Min(Qty1,$(vRnk))

Re: pick a value from table

Thu, 08 May 2014 14:34:07 GMT

thanks

but i am creating a straight table and i have my dimension and expression as below:

ID Qty

1 100

2 200

3 150

4 175

5 250

but i added second expression with above formula and then the third column is not giving me any data

please advise

thanks

Re: pick a value from table

anbu1984 — Fri, 09 May 2014 04:58:35 GMT

If you pick only 3rd rank, why did you use straight table? Can you provide expected output?

Re: Re: pick a value from table

jagan — Fri, 09 May 2014 05:44:47 GMT

Hi,

Please find attached file for solution.

Regards,

Jagan.