Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

danielco
New Contributor III

Convert an expression into Script

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

4 Replies
JustinDallas
Valued Contributor II

Re: Convert an expression into Script

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?

MVP
MVP

Re: Convert an expression into Script

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
danielco
New Contributor III

Re: Convert an expression into Script

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];

danielco
New Contributor III

Re: Convert an expression into Script

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