5 Replies Latest reply: Feb 4, 2011 8:34 AM by MariusDanauskas RSS

    "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

        • "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

            • "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.

                • "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



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

                • "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