Help with script

[Anonymous]

Any better way to write below script using apply maps or any other suggestions please

LOAD

    keySalesStatistics,

  sum([Sales Amount])/ sum([Sales Quantity])             as ItemPrice,

    if(sum([Sales Amount])/ sum([Sales Quantity])  < 2.50, '1: less than $2.50',

     if(sum([Sales Amount])/ sum([Sales Quantity])  < 5.00, '2: $2.50  to $ 4.99',

      if(sum([Sales Amount])/ sum([Sales Quantity])  < 7.50, '3: $5.00  to $ 7.49',

       if(sum([Sales Amount])/ sum([Sales Quantity])  < 10.00,'4: $7.50  to $ 9.99',

        if(sum([Sales Amount])/ sum([Sales Quantity])  < 12.50,'5: $10.00 to $12.49',

         if(sum([Sales Amount])/ sum([Sales Quantity])  < 15.00, '6: $12.50 to $14.99',

          if(sum([Sales Amount])/ sum([Sales Quantity])  < 17.50, '7: $15.00 to $17.49',

           if(sum([Sales Amount])/ sum([Sales Quantity])  < 20.00, '8: $17.50 to $19.99',

            if(sum([Sales Amount])/ sum([Sales Quantity])  < 22.50, '9: $20.00 to $22.49',

             if(sum([Sales Amount])/ sum([Sales Quantity])  < 25.00, '10: $22.50 to $24.99',

              if(sum([Sales Amount])/ sum([Sales Quantity])  < 27.50, '11: $25.00 to $27.49',

               if(sum([Sales Amount])/ sum([Sales Quantity])  < 30.00, '12: $27.50 to $29.99',

                if(sum([Sales Amount])/ sum([Sales Quantity])  < 35.00, '13: $30.00 to $34.99',

                 if(sum([Sales Amount])/ sum([Sales Quantity])  < 40.00, '14: $35.00 to $39.99',

                  if(sum([Sales Amount])/ sum([Sales Quantity])  < 45.00, '15: $40.00 to $44.99',

                               '16: $45.00+')))))))))))))))        as ItemPriceBand

from test.QVD

Group by keySalesStatistics

[Anonymous]

Try Class(var,10,'x',5 ) with var = 23 returns '15<=x<25'

LOAD *,

     Class(ItemPrice,2.5 ,'ItemPrice' )

;

LOAD

     keySalesStatistics,

  sum([Sales Amount])/ sum([Sales Quantity])             as ItemPrice

FROM from test.QVD;

[Anonymous]

Hi Eruditio, Sorry for the late reply.. i am trying to understand how that function works. Can you please show me how to write that in the attached application. So that it would be easy for me to understand....

[Anonymous · Accepted solution]

Vinay,

I made a sample code in your script with explanations.

If you still have issues, don't hesitate to reach out.

Cheers,

Antoine

[Colin-Albert]

If you are testing the CLASS function, make sure you dimension column is wide enough to display the values returned, such as:  100 <= PriceBand < 200 

[Anonymous]

Hi Eruditio, Thanks a lot man for your help... please accept me friend request. please don't mind if i tag you in any of the questions related to scripting. That is lot helpful.

[Anonymous]

Hi Antoine,  Can we set up different intervals for the class function... for example if you see in the below example after 30$ bucket ( if(sum([Sales Amount])/ sum([Sales Quantity])  < 30.00, '12: $27.50 to $29.99',). It is assigned to 35$ .if(sum([Sales Amount])/ sum([Sales Quantity])  < 35.00, '13: $30.00 to $34.99',.originally our increment was 2.5 and how can i set that up to 5$....

[Anonymous]

You can use the class function nested inside an if to take care of the upper and lower limits.

Be aware that it will reduce performance

IF ( value > x, yourstatment, if(value < Y, yourstatment, class() ) ) as ItemPriceBand