topic Re: Fractile/If Statement in Script in QlikView

Fractile/If Statement in Script

Fri, 29 May 2015 09:44:37 GMT

Hi,

I want to calculate fractile in the script. I used the following expression that doesn't work properly. If I load the script it only returns Category 4. When I calculate fractile as a dynamic dymension the formula works properly.

Do you have any idea, what's wrong with my expression?

Cheers,

Tobias

LOAD
*,
if([Ø-Price] <= fractile(TOTAL [Ø-Price], 0.25), 1,
if([Ø-Price] <= fractile(TOTAL [Ø-Price], 0.50), 2,
if([Ø-Price] <= fractile(TOTAL [Ø-Price], 0.75), 3, 4))) as Category

Re: Fractile/If Statement in Script

swuehl — Fri, 29 May 2015 09:48:15 GMT

You need a GROUP BY clause in your LOAD using the fractile() script aggregation function, right?

And you are grouping by all fields of your input table?

I don't think the TOTAL qualifier works in the script,

Re: Fractile/If Statement in Script

Fri, 29 May 2015 11:19:37 GMT

Hi Swuehl,

I have tried to apply your point including the GROUP BY clause and removing the TOTAL qualifier in my LOAD.

Doing this I get a script error stating: "Invalid Expression".

Can you the reason for this?

Thanks,

Tobias

Sales:
LOAD
PROD_MAIN_GRP_TXT,
PROD_GRP_TXT,
PROD_ID,
PROD_TXT,
MANU_TXT,
Sales,
Quantity,
COS,
Margin,
[Ø-Price]

FROM

(ooxml, embedded labels, table is Sheet1);

Sales_Temp:
LOAD
*,
if([Ø-Price] <= fractile([Ø-Price], 0.25), 1,
if([Ø-Price] <= fractile([Ø-Price], 0.50), 2,
if([Ø-Price] <= fractile([Ø-Price], 0.75), 3, 4))) as Quantil

RESIDENT Sales

GROUP BY
PROD_MAIN_GRP_TXT,
PROD_GRP_TXT,
PROD_ID,
PROD_TXT,
MANU_TXT;

DROP Table Sales;
RENAME Table Sales_Temp to Sales;

Re: Fractile/If Statement in Script

swuehl — Fri, 29 May 2015 11:29:39 GMT

The average price is not part of your group by clause, thus you need to aggregate the price when using it in your LOAD.

You don't do this when comparing the average price with the fractiles.

What do you want to achieve, specifically, in which context do you want to calculate the fractiles? I assume the TOTAL context across all data records.

Try maybe something like this:

Sales:
LOAD
PROD_MAIN_GRP_TXT,
PROD_GRP_TXT,
PROD_ID,
PROD_TXT,
MANU_TXT,
Sales,
Quantity,
COS,
Margin,
[Ø-Price]

FROM

(ooxml, embedded labels, table is Sheet1);

JOIN (Sales)
LOAD
fractile([Ø-Price], 0.25) as fractile1,

fractile([Ø-Price], 0.50) as fractile2,

fractile([Ø-Price], 0.75) as fractile3

RESIDENT Sales;

Result:

NOCONCATENATE

LOAD *,

if([Ø-Preis] <= fractile1, 1,
if([Ø-Preis] <= fractile2, 2,

if([Ø-Preis] <= fractile3, 3, 4))) as Quantil

RESIDENT Sales;

Drop table Sales,

drop fields fractile1, fractile2. fractile3;

Re: Fractile/If Statement in Script

jonathandienst — Fri, 29 May 2015 11:32:30 GMT

You need to include all non-aggregate expressions in the group by. Since are performing a LOAD *, you will need to add these as well:

Sales,
Quantity,
COS,
Margin

Re: Fractile/If Statement in Script

Fri, 29 May 2015 12:37:43 GMT

Thanks so far for your help. I try to explain what should be the final result:

I want to cluster average article sales prices of various manufacturers by product group into fractiles. The final result should be a matrix, that shows which manufacturer is offering which products in the different fractiles (price segment).

My source data is segregated by Manufacturer, Product Group and Articles. The results of your script are perfectly matching the results I get, when I did the calculation using a dynamic dimension.

But it doesn't work if I set filter e.g. by Product Group, or any other Dimension. I guess the fractiles need to be calculated for each Dimension i.e. Product Group.

Is there a "smart" way, how to do this?

Re: Fractile/If Statement in Script

swuehl — Fri, 29 May 2015 12:42:43 GMT

You can add a group by clause with the appropriate dimensions to the script:

JOIN (Sales)
LOAD

Dim1,

Dim2,
fractile([Ø-Price], 0.25) as fractile1,

fractile([Ø-Price], 0.50) as fractile2,

fractile([Ø-Price], 0.75) as fractile3

RESIDENT Sales

GROUP BY Dim1, Dim2 ;

Replace Dim1 and Dim2 with the appropriate fields from your sales table.

Be aware that calculating these fractiles in the script prevent you from getting adjusted results when filtering records.

Maybe a UI solution (expressions in front end) would be better then.