Calculate and Assign Decile on load

[#1]

Hi There

I have a dataset which contains customers and their associated profit value.

I am trying to create a decile view of this data by assigning each customer a decile value during the load script.

Whilst researching this problem in the QlikView Community I have managed to reuse a calculated dimension suggested for a similar problem.  The expression is formatted like this;

=IF(PROFIT <= FRACTILE(TOTAL PROFIT, 0.1), 'Decile01',

  IF(PROFIT <= FRACTILE(TOTAL PROFIT, 0.2), 'Decile02',

  IF(PROFIT <= FRACTILE(TOTAL PROFIT, 0.3), 'Decile03',

...etc

....and this works for charts and tables and dynamic interaction (well almost - the distribution isnt exactly in 10% chunks but close).

However, I want to able able to assign the Decile value\name as a fixed attribute for each customer during the load process so I'd have a new column called "DecileNumber" or similar.  Using a calculated dimension in the QVW layer doesn't give me the a fixed value that I can work with.

My real data set is quite large (i.e. several million customers).

Any suggestions appreciated!

JW

[Kushal_Chawda]

Have you tried?

Load customer,

F(PROFIT <= FRACTILE(TOTAL PROFIT, 0.1), 'Decile01',

  IF(PROFIT <= FRACTILE(TOTAL PROFIT, 0.2), 'Decile02',

  IF(PROFIT <= FRACTILE(TOTAL PROFIT, 0.3), 'Decile03'))) as Decile

From table

group by customer

[Anonymous]

Hi Jason,

for script side load see attached example

You will get idea how to do this.

you need to modify the expression as required

Thanks

BKC

[#4]

Hi

Thank you for the response.  When I use the suggested script I get a failure on load;

Aggregation expression required by Group by clause

JW

[#5]

Hi There

Thank you.  I have tried this and it almost works but I can't get the nested IF statement to work.

Do you have any ideas on what the syntax should be?

Thanks

JW

[Anonymous]

Can you explain if you are getting error on your nested If or FRACTILE() function is causing the issue ?

Thanks

BKC

[#7]

Hi Balkumar

I am working with your example QVW and I have updated example to include 10 customers (A to K). 

In your exampke you commented that I needed to "Input my IF Statement here" but I have got stuck trying expand the IF statement in your example QVW by using IF statements applied to the vTotalSales variable that you created.  When that didnt work I also tried using the FRACTILE statements which I applied to TOTAL Sales.

The attached text file shows the 4 examples I have tried (commented) and their outcomes.  None of these worked.

Based on these 10 example customers and the way which I have applied the example sales value, the desired outcome would be for each customer to be in 1 of the deciles. Of course my "real" problem data is customer and profit but if I can get your example QVW to display what I need then I can adapt to my real problem.

Thanks

JW