problem with sum expression

[#1]

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

[Anonymous]

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

[tajmohamed30]

whether the last closing braces required

[Anonymous]

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

[#5]

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"

[CarlosAMonroy]

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

[#7]

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

[CarlosAMonroy]

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