Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Qlik Certification Exam Sale - 40% off. Register by June 30, 2020. GET DETAILS
Highlighted
Not applicable

problem with sum expression

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

7 Replies
Highlighted
Contributor III

Re: problem with sum expression

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

Highlighted
Contributor III

Re: problem with sum expression

whether the last closing braces required

Highlighted
Contributor III

Re: problem with sum expression

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

Highlighted
Not applicable

Re: problem with sum expression

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"

Highlighted
Contributor III

Re: problem with sum expression

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

Highlighted
Not applicable

Re: problem with sum expression

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

Highlighted
Contributor III

Re: problem with sum expression

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