7 Replies Latest reply: May 16, 2014 5:06 AM by Antoine Frangieh RSS

    Help with script

    Vinay kumar Bangari

      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