Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

krishna20
Valued Contributor II

Expression in script

Hi Friends,

I need to calculate simple division at script level but,the fields from different tables.I have used mapping concept to achieve this. Is this correct approach. when i reloads the script raising error as "Invalid Expression". Can anyone please suggest me where i goes wrong.

PolA:

MAPPING LOAD

     TP_BI_ID,

     TP_DISC_LC_1 as POL_DISC_LC_1;

FROM

[..\QVD\Pol.QVD]

(qvd);

Risk:

LOAD * ,

(SUM(trim(POL_DISC_LC_1))/COUNT(DISTINCT TR_RISK_SR_NO)) as RISK_DISC_LC_1;

LOAD 

     TR_BI_ID,

     TR_TP_BI_ID,

     TR_RISK_SR_NO,

ApplyMap ('PolA',TR_TP_BI_ID,Null()) as POL_DISC_LC_1

FROM

[..\QVD\RISK.QVD]

(qvd);

Regards

Krishna

1 Solution

Accepted Solutions
mov
Esteemed Contributor III

Re: Expression in script

Try this.  I don't know your logic, just fixing technicalities to make it work.

Risk:

LOAD

     TR_BI_ID,

     TR_COMP_CODE,

     TR_LOB_CODE,

     TR_POL_TRANS_ID,

     TR_POL_TRAN_SR_NO,

     TR_DEPT_CODE,

     SUM(trim(ApplyMap ('Policy_MAP', TP_BI_ID, Null())) /COUNT(DISTINCT TR_RISK_SR_NO) as RISK_DISC_LC_1

GROUP BY

     TR_BI_ID,  

     TR_COMP_CODE,

     TR_LOB_CODE,

     TR_POL_TRANS_ID,

     TR_POL_TRAN_SR_NO,

     TR_DEPT_CODE;

LOAD

     TR_BI_ID,

     TR_TP_BI_ID as TP_BI_ID,

     TR_COMP_CODE,

     TR_LOB_CODE,

     TR_POL_TRANS_ID,

     TR_POL_TRAN_SR_NO,

     TR_DEPT_CODE,

     TR_RISK_SR_NO

FROM (qvd);

View solution in original post

28 Replies
MVP & Luminary
MVP & Luminary

Re: Expression in script

Risk:

LOAD * ,

(SUM(trim(POL_DISC_LC_1))/COUNT(DISTINCT TR_RISK_SR_NO)) as RISK_DISC_LC_1;

If you use aggregation functions like sum and count then all the fields not used in those functions must be included in the GROUP BY clause.


talk is cheap, supply exceeds demand
Not applicable

Re: Expression in script

So you can use COUNT in the script?

krishna20
Valued Contributor II

Re: Expression in script

Hi Gysbert,

Thank you for the reply.

I applied Group By clause as below.But , still it's raising the same error.Please, suggest me.

LOAD * ,

(SUM(trim(POL_DISC_LC_1))/COUNT(DISTINCT TR_RISK_SR_NO)) as RISK_DISC_LC_1

Group BY POL_DISC_LC_1 ,TR_RISK_SR_NO;

MVP & Luminary
MVP & Luminary

Re: Expression in script

Yes, you can. Here are two examples:

OrderTotal:

LOAD

      count(OrderID) as NoOfOrders

FROM MyOrders.qvd (qvd);

OrdersPerCustomer:

LOAD

     Customer,

     count(OrderID) as NoOfCustomerOrders

FROM MyOrders.qvd (qvd)

ORDER BY Customer;


talk is cheap, supply exceeds demand
krishna20
Valued Contributor II

Re: Expression in script

Hi Lewis,

Yes I need to use count at this part.

Eg;

SUM(A)/Count(B)

krishna20
Valued Contributor II

Re: Expression in script

Hi Gysbert,

I calculated count separately and passing that field in the preceding load Calculation.Its raising the same error.Cal.png

Highlighted
anbu1984
Honored Contributor III

Re: Expression in script

Load

TR_BI_ID,

     TR_TP_BI_ID,

(SUM(trim(POL_DISC_LC_1))/COUNT(DISTINCT TR_RISK_SR_NO)) as RISK_DISC_LC_1

Group BY

TR_BI_ID,

     TR_TP_BI_ID

MVP & Luminary
MVP & Luminary

Re: Expression in script

You need to add the fields NOT used in the aggregation functions to the GROUP BY clause.


talk is cheap, supply exceeds demand
krishna20
Valued Contributor II

Re: Expression in script

Hi,

Still i'm getting the error, i'm not  getting where i goes wrong please suggest me.

Please find the attachment of error image and sample app.(consider at Risk tab)