Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
Please ignore the line "I tried doing it at script level by:" while understanding the problem, its a mistake. Thanks!
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>