Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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.
So you can use COUNT in the 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;
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;
Hi Lewis,
Yes I need to use count at this part.
Eg;
SUM(A)/Count(B)
Hi Gysbert,
I calculated count separately and passing that field in the preceding load Calculation.Its raising the same error.
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
You need to add the fields NOT used in the aggregation functions to the GROUP BY clause.
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)