2 Replies Latest reply: Jun 27, 2011 9:40 PM by AXON SG RSS

    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!