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

[#2]

Pol:

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

i just changed the table names

try this ones

[krishna20]

Hi,

Thank you for the reply. Without calling Mapping table in the apply map is it works?

[maxgro]

group by is missing, not aggregated field must be in group by

load

     field1,

     field2,

     field3,

     count(...),

     sum(.....)

resident ...

group by

     field1,

     field2,

     field3;

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,

     COUNT(DISTINCT TR_RISK_SR_NO) as COUNT_RISK_SR_NO,

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

FROM

(qvd)

group by

    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

;

[Anonymous]

Krishna,

Two points:

1. You're using the applymap() correctly.

2.  The problem is with aggregation and group by.  The rule of thumb is - all fields that are not used in aggregation, must be used in the GROUP BY.  Simple example:

Table:

LOAD

A,

B,

C,

sum(X) as SumX

FROM ...

GROUP BY A,B,C;

Here field X is used in aggregation (sum in this case), but A, B, and C are not.  So, they must be used in the GROUP BY.

Regards,

Michael

[krishna20]

Hi,

Thank you for the suggestion.Exactly, I'm missing the Group BY where to place correctly and fields add into this.please find the error image.

[#7]

when mapping tables aren used to map a certain field value or expression,that value is compared to the value in the first column of the mapping table.if found,the original value is replaced by the corresponding value in the second of the mapping table.if not found,no replecement is made...

[Anonymous]

Almost there...

Remove as ... from the "Group By" part

Remove the last line, TR_RISK _SR_NO from there too - it is inside aggregation.

[Anonymous]

Just noticed: you have TR_RISK _SR_NO inside and outside the aggregation - it doesn't make sense.  Remove the field from the outside of the aggregation.

[krishna20]

Hi Michael,

still in a little bit confusion.I'm using TR_RISK_SR_NO for other calculations.That's the reason for not commenting.

Risk:

LOAD *,

TR_BI_ID,

     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,

(SUM(trim(POL_DISC_LC_1))/COUNT_RISK_SR_NO) as RISK_DISC_LC_1

GROUP BY

TR_BI_ID,

     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 ;

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,

     COUNT(DISTINCT TR_RISK_SR_NO) as COUNT_RISK_SR_NO,

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

FROM

(qvd);

Please suggest me.

[Anonymous · Accepted solution]

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