Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Selecting top 10 rows at script level, to avoid fading out of "Always select one value" by using expression in listbox

Hi There,

My table contains Material ID, Material Description, Selling Price, Transection Type (Billing/Booking), and Commodity Type (Copper/Lead) for each transection.

I tried doing it at script level by:

I wanted to create a listbox of top 10 parts (by highest total selling price), showing material description for top 10 parts. For this I used the expression

If(aggr(rank(sum(SALES_AMT_ASP)),MATERIAL_DESC_ASP)<=10,MATERIAL_DESC_ASP,null())AS MATERIAL_DESC_ASP and it worked.

But now that I am using expression in Listbox, the option of "always select one value" is fading out, which is must to have. To avoid it, I learned that I should do it at the script level.

ASP_Top_10:

LOAD If(aggr(rank(sum(SALES_AMT_ASP)),MATERIAL_DESC_ASP)<=10,MATERIAL_DESC_ASP,null())AS MATERIAL_DESC_ASP

'Billing' as Rev_Type

'Lead' as Commodity_ASP

PART_NO & MATERIAL DESCRIPTION AS Top_10

RESIDENT sales_po

where rev_type='billing'

and commodity_asp='Lead'

ORDER BY MATERIAL_DESC_ASP

Idea is to make list a list box of Top_10 and select option "always one selected value" which would be then visible i guess.

But now i am getting error that aggr statement cant be used in LOAD statement, could there be any better way to do it? I am new to SQL and not sure if its the way.

Each help will be highly appreciated. Thanks!

2 Replies
Not applicable
Author

Please ignore the line "I tried doing it at script level by:" while understanding the problem, its a mistake. Thanks!

Not applicable
Author

im not sure if there's a workaround in script level

but i believe you can simply load all the rows and do the condition in the list box's properties > General > Field> select <Expression>