3 Replies Latest reply: May 4, 2016 2:40 PM by Ajay Gupta RSS

    If then Else

    Ajay Gupta

      Hi I am getting an expression error for this

       

      if [ALCOHOL_Every.day] not like 'NA' then

      [ALCOHOL_Every.day]

      else 1=1

      end if

       

      What am I doing wrong here.

      The values of ALCOHOL_Every.day ranges from 0 to 10 and I am seeing the extra value NA which I want to remove. What am I doing wrong in this ?

      Thanks

      Aj

        • Re: If then Else
          Sunny Talwar

          Is this for a dimension? May be try this:

           

          If([ALCOHOL_Every.day] <> 'NA', [ALCOHOL_Every.day], 1)

          • Re: If then Else
            Stefan Wühl

            There are multiple things to consider:

             

            a) If..Then... Else..End if are control flow statements you can use in the script (not in the front end), but if you are using them in the script, you can't use them within a LOAD statement. Hence you can't really use a field like [ALCOHOL_Every.day] in the if condition.

             

            b) If you want to negate a boolean, something like FIELD LIKE 'NA', you need to write NOT FIELD LIKE 'NA'

             

            c) I don't really understand what 1=1 in your ELSE branch is made for.

             

            So, depending where you want to remove the value 'NA', try something like this in the script:

             

            LOAD

                 If([ALCOHOL_Every.day] <> 'NA', [ALCOHOL_Every.day] ) as [ALCOHOL_Every.day],

                 ...

             

            or maybe (if you want to remove the complete record:

             

            LOAD

                 [ALCOHOL_Every.day],

                 ...

            FROM ...

            WHERE [ALCOHOL_Every.day] <> 'NA';


             

            In the front end, you can either use a calculated dimension like

             

            = If([ALCOHOL_Every.day] <> 'NA', [ALCOHOL_Every.day] )

             

            or set analysis in all your aggregations:

             

            =Sum({<[ALCOHOL_Every.day] -= {'NA'}>} Value)