"if" doesn't work like expected

[#1]

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

[#2]

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

[#3]

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.

[#4]

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

[#5]

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

[#6]

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