9 Replies Latest reply: Sep 25, 2017 4:47 PM by Jonathan Vitale RSS

    Problem using IF Statement

    Eduardo DImperio

      Hi !

       

      For some reason when a cut and paste a "IF" that's works on chart, its doesn't work on my script. I get "Invalid Expression" message and i don't find the error.

       

      Any Help Please

       

      DEFEITO:

       

      LOAD

       

      IF(Ultimadataleitura<today()-1 OR isnull(Ultimadataleitura) and statusConexao=0, OID_SYSTEM) AS OID_SYSTEM,

      IF(isnull(max(DATE_READ)) AND COUNT(DISTINCT OID_UC)=0,'-',

      IF(isnull(max(DATE_READ)) AND COUNT(DISTINCT OID_UC)<QTDE_UC AND COUNT(DISTINCT CONCENTRATOR)>1,'SUM(s) COM PROBLEMA PONTUAIS HÁ MAIS DE 5 DIAS',

      IF(COUNT (DISTINCT OID_UC)=QTDE_UC,'PREDIO INTEIRO COM PROBLEMAS',

      IF(COUNT(DISTINCT CONCENTRATOR)=1,'SUM(s) '&CONCENTRATOR&' COM PROBLEMA PONTUAIS HÁ '&FLOOR(TODAY()-MAX(DATE_READ),1)&' DIAS ',

      IF(COUNT(DISTINCT OID_UC)<QTDE_UC,'SUM(s) COM PROBLEMA PONTUAIS HÁ '&FLOOR(TODAY()-MAX(DATE_READ),1)&' DIAS','TODAS SUMs COM PROBLEMA HÁ MAIS DE 5 DIAS - VERIFICAR TELEMETRIA'

          )

          )

          )

          )

          ) AS LAUDO

        • Re: Problem using IF Statement
          Jonathan Vitale

          Is there more to your script? There should be something after the Load statement to tell us where you are loading from, such as an external csv file or a previously loaded Resident table.

            • Re: Problem using IF Statement
              Eduardo DImperio

              Indeed Jonathan,

               

              My fault.

               

               

              DEFEITO:

               

              LOAD

               

              if(isnull(max(DATE_READ)) AND COUNT(DISTINCT OID_UC)=0,'-',

              If(isnull(max(DATE_READ)) AND COUNT(DISTINCT OID_UC)<QTDE_UC AND COUNT(DISTINCT CONCENTRATOR)>1,'SUM(s) COM PROBLEMA PONTUAIS HÁ MAIS DE 5 DIAS',

              if(COUNT (DISTINCT OID_UC)=QTDE_UC,'PREDIO INTEIRO COM PROBLEMAS',

              if(COUNT(DISTINCT CONCENTRATOR)=1,'SUM(s) '&CONCENTRATOR&' COM PROBLEMA PONTUAIS HÁ '&FLOOR(TODAY()-MAX(DATE_READ),1)&' DIAS ',

              if(COUNT(DISTINCT OID_UC)<QTDE_UC,'SUM(s) COM PROBLEMA PONTUAIS HÁ '&FLOOR(TODAY()-MAX(DATE_READ),1)&' DIAS','TODAS SUMs COM PROBLEMA HÁ MAIS DE 5 DIAS - VERIFICAR TELEMETRIA'

                   )

                   )

                   )

                   )

                  ) AS LAUDO

              RESIDENT AUX;

               

               

              DROP TABLE AUX;

            • Re: Problem using IF Statement
              Bill Markham

              In the script if you use an aggregation function like max then there needs to be a Group By in the load statement.

               

              I guess that may be the issue.

              • Re: Problem using IF Statement
                Jonathan Vitale

                @Eduardo

                I think your problem (or one problem) is in the  fourth If statement:

                 

                     if(COUNT(DISTINCT CONCENTRATOR)=1,'SUM(s) '&CONCENTRATOR&' COM


                "CONCENTRATOR" is a field with multiple values (I assume). You are checking to see if there is only one distinct value and then you hope to use that value in the resulting text. But, even though YOU know that CONCENTRATOR can be only one thing, the compiler doesn't. It thinks you are trying to stick an entire set of field names in a string.

                 

                Try

                Only(CONCENTRATOR) to let the compiler know that you are looking for a single value.

                 

                I also think you probably need to use a Group by clause. If you want the entire data to be your group you could make a column with a single value ("1 as MyGroup") and then group by that.

                 

                Jonathan

                  • Re: Problem using IF Statement
                    Eduardo DImperio

                    Hi Jonathan unfortunately like i said before,this code give me error too:

                     

                     

                    DEFEITO:

                     

                     

                    LOAD

                     

                    IF(isnull(max(DATE_READ)) AND COUNT(DISTINCT OID_UC)=0,'-',

                    IF(isnull(max(DATE_READ)) AND COUNT(DISTINCT OID_UC)<QTDE_UC,1,0

                        )

                        ) AS LAUDO

                     

                     

                    But i'll try to use some group by and see what happen.

                     

                    Thank you for your time

                      • Re: Problem using IF Statement
                        Jonathan Vitale

                        No, now I'm pretty sure Group By won't help you. When you use an aggregate function without a Group By clause, it assumes that your group is the whole data, and then it tries to return just one row. When you mix this with multiple row data it throws an error because you are working at two levels of granularity.

                         

                        Here's an example:

                         

                        TA:

                        Load *

                        Inline

                        [

                        Index, Group, Value

                            1, A, 100

                            2, A, 100

                            3, A, 100

                        ];

                         

                         

                        TB:

                        Load

                        // Index,   <--- Including Index throws an error

                        IF(Count(distinct Value) = 1, Only(Value), Null()) as ValueDistinct

                        Resident TA;

                         

                        To create the "ValueDistinct", I am checking to see if there is only one distinct value of "Value". If so I return that value with the Only function, else I return Null. When loaded table TB will be a Single Row, because - presumably - using the Count function automatically aggregated on all of the data. Note, that if I try to use the Index field in TB I get an error because we'd be working at two different levels. Also, if you replace "Only(Value)" with a static value, such as 100, you'll get the same error. Just by calling Count in the conditional expression, you will aggregate to the group level (in this case all data).

                         

                        I see what you are trying to do: you want to maintain all of your rows, but still use information from the entire data set to make decisions. Probably what you will need to do is make a reference table that includes the counts and max values that are of interest to you. Then use the peek function to compare each value in your complete table to those values in the reference table.

                         

                        Here's an example, assuming I have TA from above:

                         

                        Ref:

                        Load

                        Count(distinct Value) as NumDistinct

                        Resident TA;

                         

                        TC:

                        NoConcatenate

                        Load

                        Index as NewIndex,

                        Peek(NumDistinct, -1, 'Ref') as NumDistinctPeek,

                        IF(Peek(NumDistinct, -1, 'Ref') = 1, Value, Null()) as ValueDistinct

                        Resident TA

                         

                        In this case I am using the value from the "Ref" table (a count of distinct value) as a variable for comparison in the second table. The second table will now maintain all of my rows because there is no implied aggregation.

                         

                        I hope this helps,

                         

                        Jonathan