Convert an expression into Script

[uko223]

Hi, I have this expression in my app that I need to do in the script but I am having trouble whit the syntax

aggr(
if(Exp_Rolling12_Units_Sold>=fractile(total aggr(Exp_Rolling12_Units_Sold,[Deliver/ShipTo Account],[Item - IH Brand]),.75), 'Q1',
if(Exp_Rolling12_Units_Sold>=fractile(total aggr(Exp_Rolling12_Units_Sold,[Deliver/ShipTo Account],[Item - IH Brand]),.5), 'Q2',
if(Exp_Rolling12_Units_Sold>=fractile(total aggr(Exp_Rolling12_Units_Sold,[Deliver/ShipTo Account],[Item - IH Brand]),.25), 'Q3'
, 'Q4')))
,[Deliver/ShipTo Account],[Item - IH Brand])

When I put it into the script, I get "Invalid Expression".

[JustinDallas]

AGGR is more of a charting function.  I think what you are looking for is using "Group By" in your script instead.  Do you have any testdata that we might be able to experiment with?

[jonathandienst]

Aggr() is an expression only function.

There is no direct script equivalent for an aggr() used this way. Your aggr() expression returns a matrix of values for the two dimensions. Qlik script has no way to represent and operate on a matrix.

You would have to refactor this into several script steps to create the matrix in a table and populate the table.

[uko223]

Understood, I did already try the to put it in the script as below but it does not appear to be working. Not sure it 'fractile' works in the script. 

tmp:
Load
*,
if(Exp_Rolling12_Units_Sold>=fractile(Exp_Rolling12_Units_Sold,.75), 'Q1',
if(Exp_Rolling12_Units_Sold>=fractile(Exp_Rolling12_Units_Sold,.5), 'Q2',
if(Exp_Rolling12_Units_Sold>=fractile(Exp_Rolling12_Units_Sold,.25), 'Q3'
, 'Q4')))as Quartile

Resident LinkSales

Group By [Deliver/ShipTo Account],[Item - IH Brand];

[uko223]

 I get this error

The following error occurred:

Invalid expression

The error occurred here:

tmp_SalesOrders: NoConcatenate Load [Deliver/ShipTo Account], [Item - IH Brand], Exp_Rolling12_Units_Sold, if(Exp_Rolling12_Units_Sold>=fractile(Exp_Rolling12_Units_Sold,.75), 'Q1', if(Exp_Rolling12_Units_Sold>=fractile(Exp_Rolling12_Units_Sold,.5), 'Q2', if(Exp_Rolling12_Units_Sold>=fractile(Exp_Rolling12_Units_Sold,.25), 'Q3' , 'Q4')))as Quartile Resident LinkSales Group By [Deliver/ShipTo Account],[Item - IH Brand]

Saving app...