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