Expression in script

[krishna20]

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

[Gysbert_Wassenaar]

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.

[#3]

So you can use COUNT in the script?

[krishna20]

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;

[Gysbert_Wassenaar]

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;

[krishna20]

Hi Lewis,

Yes I need to use count at this part.

Eg;

SUM(A)/Count(B)

[krishna20]

Hi Gysbert,

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

[anbu1984]

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

[Gysbert_Wassenaar]

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

[krishna20]

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)