topic Re: problem with sum expression in QlikView

problem with sum expression

Wed, 25 Nov 2020 16:16:04 GMT

Hi,

My name is Christian and I have a problem with an QlikView expression.

MySQL condition:

WHERE

START_DATE BETWEEN selDateFrom AND selDateTo

AND EXISTS(

SELECT SUBSCRIPTION_ID

FROM

t_payment pay

WHERE

pay.SUBSCRIPTION_ID = sub.SUBSCRIPTION_ID

AND OPERATOR_ID IN(I_OPERATOR)

AND DATE(pay.TIME_STAMP) = DATE(sub.START_DATE)

AND pay.PRICE > 0)

My QlikView expression:

sum(if(START_DATE_FORMAT >=V_Start_date and START_DATE_FORMAT <V_End_date and sum(if(SUBSCRIPTION_ID=PAYMENT_SUBSCRIPTION_ID and PAY.TIME_STAMP_FORMAT=START_DATE_FORMAT and PRICE>0,1,0))>0,1,0)) but it doesn't work.

Can you help me please?

Thanks,

Christian

Re: problem with sum expression

Anonymous — Tue, 06 Jun 2017 12:16:14 GMT

Hi Christian,

What are you trying to SUM?

But The syntax for nested if is as below:

if(Logical test and/or Logitical test,value if true,if(next logical test and/or another test,value if true,if(3rd logical test,value if true,value if false)))))

Using your fields above. Something like the below should work:

IF(START_DATE_FORMAT>=V_Start_date AND START_DATE_FORMAT < V_End_date,1,if(SUBSCRIPTION_ID=PAYMENT_SUBSCRIPTION_ID AND PAY.TIME_STAMP_FORMAT=START_DATE_FORMAT AND PRICE>0,1,0)))

Re: problem with sum expression

tajmohamed30 — Tue, 06 Jun 2017 12:35:42 GMT

whether the last closing braces required

Re: problem with sum expression

Anonymous — Tue, 06 Jun 2017 12:36:52 GMT

It might need more brackets, just check, while typing the expression, if one of the first brackets are red, it means more are needed

Re: problem with sum expression

Tue, 06 Jun 2017 12:48:29 GMT

it is important to count this type of record, but the result is '-'.

In my expression there aren't red brackets.

It is a big condition and the last part is a "SQL exists"

Re: problem with sum expression

CarlosAMonroy — Tue, 06 Jun 2017 14:15:19 GMT

Hi Christian,

Why don't you do the same SQL where clause in the QV script?

And then create the flag and in the front end just sum the flag.

Something like this:

TableName:

Load Field1,Field2, FieldN, 1 as _FlagName;

SQL SELECT *

FROM source

WHERE

START_DATE BETWEEN selDateFrom AND selDateTo

AND EXISTS(SELECT SUBSCRIPTION_ID

FROM t_payment pay

WHERE

pay.SUBSCRIPTION_ID = sub.SUBSCRIPTION_ID

AND OPERATOR_ID IN(I_OPERATOR)

AND DATE(pay.TIME_STAMP) = DATE(sub.START_DATE)

AND pay.PRICE > 0)

;

Or you can translate the SQL condition and use it in QV script.

Hope that helps.

Thanks,

Carlos M

Re: problem with sum expression

Tue, 06 Jun 2017 15:15:42 GMT

yes but i prefer to use an expression in my report. I think that I have a sintax error. Other solution?

Re: problem with sum expression

CarlosAMonroy — Tue, 06 Jun 2017 15:39:53 GMT

If you can share a qvw example would be great. anyways the If's conditions that you are using are not correct.

You should try something like:

sum(if(START_DATE_FORMAT >= V_Start_date and START_DATE_FORMAT < V_End_date)

and SUBSCRIPTION_ID=PAYMENT_SUBSCRIPTION_ID

and PAY.TIME_STAMP_FORMAT=START_DATE_FORMAT

and PRICE>0,1,0))

Hope that helps,

Carlos M