Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

vinay_bangari
Valued Contributor III

Help with script

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

1 Solution

Accepted Solutions
eruditio
Contributor III

Re: Re: Re: Help with script

Vinay,

I made a sample code in your script with explanations.

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

Cheers,

Antoine

7 Replies
eruditio
Contributor III

Re: Help with script

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;

vinay_bangari
Valued Contributor III

Re: Re: Help with script

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....

eruditio
Contributor III

Re: Re: Re: Help with script

Vinay,

I made a sample code in your script with explanations.

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

Cheers,

Antoine

Re: Help with script

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 

vinay_bangari
Valued Contributor III

Re: Re: Re: Help with script

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.

vinay_bangari
Valued Contributor III

Re: Re: Re: Help with script

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$....

eruditio
Contributor III

Re: Help with script

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

Community Browser