Qlik Community

QlikView Deployment

Discussion Board for collaboration related to QlikView Deployment.

Not applicable

"if" doesn't work like expected

Hello everybody

I am trying to make a straight table with expressions to count records that meet conditions. People say that sum() is better than count(). So i made nested statement:

sum ((if(table1.SEGMENTAS='Maza zala',if(table2.TVIRTINIMOFLAG=1,1,0),0)))

but it gives a result of 0 when I'm sure that it has to be more. Then i tried to make it other way:

sum(if( table1.SEGMENTAS = 'Maza zala' and table2.TVIRTINIMOFLAG = 1,1,0))

but i stil get 0. I tried various ways of syntax, but all i get null or zero. Since other expressions(with one condition) in the same straight table are working fine, i dont think that problem lies in load script. Can somebody tell where is my problem?
Is it a problem, that one condition is in one table, and the other in another table? but since i can select both fields in expression editor window, it should not be a problem.

Thanks in advance. Marius

5 Replies
Not applicable

"if" doesn't work like expected

Hello Marius,

You need to write something like sum(if(table1.SEGMENTAS='Maza zala',1,if(table2.TVIRTINIMOFLAG=1,1,0))) First wrtie the IF statement and THEN Expression which is the result and if not ELSE expression which could be another if statement.

Hope you understood.

Mama

Not applicable

"if" doesn't work like expected

Thanks for reply, but i need to sum where both conditions are met. like operator AND. If the first condition is true, then if the second condition is true, then and only then i need to add 1 to total sum. All other cases are not good. In Your expression it is like OR and although it gives a result, but it is not good in my case. Maybe QlikView does not supportTHENIF? I suppose the second my expression would be better, but i just don't understand why it is not working.

Not applicable

"if" doesn't work like expected

Hello Marius,

Something like below worked for me, can you check if this works for you aswell? I have used and here and its fine, instead of > sign you may use the = and define your value....please try to play around this and check if it helps.

=if((sum(INV_SELLING_UNITS)>11 and sum(INV_CURRENCY)>0),INV_SELLING_UNITS,INV_CURRENCY)

Mama



Not applicable

"if" doesn't work like expected

Hi

Use this

=If(Fields1='Abc' and Fileds2=1,'1','0')

is the syntax.

If you want to do sum use

=sum(if(Field1='A' and Field2='B',Amt))

Not applicable

"if" doesn't work like expected

thank you guys for help and sorry for wasting your time. the problem was actually in load script, because instead of using outer join i used table3 to connect table1 and table2. it looks something like this:

table1:
users1,
segmentas.

table2:
users2,
tvirtinimoflag.

table3:
users_from_both_tables

but table1 does not have all users from table2 and vice versa. so if i check SEGMENTAS, the connection goes through USERS1 to USERS_FROM_BOTH_TABLES to USERS2 and does not find USERS1=USERS2. My stupidity wasted two days of my life. most likely people who work with databases instinktively know that it is wrong, and i had to understand it in the hard way

Community Browser