topic Re: Help with script in QlikView

Help with script

Anonymous — Wed, 14 May 2014 15:10:14 GMT

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

Re: Help with script

Anonymous — Wed, 14 May 2014 15:16:02 GMT

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;

Re: Re: Help with script

Anonymous — Wed, 14 May 2014 17:43:16 GMT

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

Re: Re: Re: Help with script

Anonymous — Wed, 14 May 2014 21:35:01 GMT

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

Colin-Albert — Wed, 14 May 2014 21:55:36 GMT

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

Re: Re: Re: Help with script

Anonymous — Thu, 15 May 2014 12:45:42 GMT

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.

Re: Re: Re: Help with script

Anonymous — Thu, 15 May 2014 20:48:46 GMT

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

Re: Help with script

Anonymous — Fri, 16 May 2014 09:06:36 GMT

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