topic Re: Expression in script in QlikView

Expression in script

krishna20 — Wed, 29 Apr 2015 11:16:29 GMT

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

Re: Expression in script

Gysbert_Wassenaar — Wed, 29 Apr 2015 11:28:38 GMT

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.

Re: Expression in script

Wed, 29 Apr 2015 11:45:08 GMT

So you can use COUNT in the script?

Re: Expression in script

krishna20 — Wed, 29 Apr 2015 11:47:20 GMT

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;

Re: Expression in script

Gysbert_Wassenaar — Wed, 29 Apr 2015 11:49:02 GMT

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;

Re: Expression in script

krishna20 — Wed, 29 Apr 2015 11:49:19 GMT

Hi Lewis,

Yes I need to use count at this part.

Eg;

SUM(A)/Count(B)

Re: Expression in script

krishna20 — Wed, 29 Apr 2015 12:04:20 GMT

Hi Gysbert,

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

Re: Expression in script

anbu1984 — Wed, 29 Apr 2015 12:10:58 GMT

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

Re: Expression in script

Gysbert_Wassenaar — Wed, 29 Apr 2015 12:19:18 GMT

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

Re: Expression in script

krishna20 — Wed, 29 Apr 2015 18:54:57 GMT

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)

Re: Expression in script

Wed, 29 Apr 2015 19:04:35 GMT

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

Re: Expression in script

krishna20 — Wed, 29 Apr 2015 19:12:39 GMT

Hi,

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

Re: Expression in script

maxgro — Wed, 29 Apr 2015 19:13:19 GMT

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

;

Re: Expression in script

Anonymous — Wed, 29 Apr 2015 19:18:41 GMT

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

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

Re: Expression in script

krishna20 — Wed, 29 Apr 2015 19:22:57 GMT

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.

Re: Expression in script

Wed, 29 Apr 2015 19:23:53 GMT

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

Re: Expression in script

Anonymous — Wed, 29 Apr 2015 19:27:09 GMT

Almost there...

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

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

Re: Expression in script

Anonymous — Wed, 29 Apr 2015 19:30:18 GMT

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.

Re: Expression in script

krishna20 — Wed, 29 Apr 2015 19:51:51 GMT

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.

Re: Expression in script

Anonymous — Wed, 29 Apr 2015 20:11:55 GMT

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