6 Replies Latest reply: Jan 18, 2017 4:42 PM by Wallo Atkinson RSS

    IF Statement Calculation

    Zeth Baker

      I have the following If statement in the load script but it is an invalid expression. Does anyone have any thoughts on how i can make this work?

       

      IF((Match(Type,'FTE') and sum(Number)) > (Match(Type,'ACTUAL') and sum(Number)),'OVERSTAFFED','UNDERSTAFFED')

        • Re: IF Statement Calculation
          Krishnapriya Arumugam

          You should set a flag for Type = FTE and using the Flag you can create a expression in UI.

           

          Else you can use below the expression in UI

           

          if(sum({<Type={"FTE"}>}Number)>sum({<Type={"ACTUAL"}>}Number),'OVERSTAFFED','UNDERSTAFFED')

           

          Are you using the expression in Load statement or assigning the value to a variable?

          • Re: IF Statement Calculation
            Handry Orozco

            Hi Zeth

             

            Please, share your associative model data

            • Re: IF Statement Calculation
              Digvijay Singh

              It won't work so easily in script with the reason of having aggregation involved, you may try as below -

              1. First calculate sum(number) using where clause and group by clause in separate table

              2. join the sum no to the old table so that sum field is available in all records

              3. then compare the type field with the new sum field as you are trying to do.

               

              May not completely correct, if you share your script, can add something further.

               

              Regards,

              • Re: IF Statement Calculation
                Wallo Atkinson

                Zeth,

                It's hard to say without more info.  Making some assumptions you could do something like this:

                 

                Data:

                LOAD * INLINE [

                    Team, Type, Number

                    A, Actual, 4

                    A, Actual, 3

                    A, FTE, 6

                    B, Actual, 1

                    B, Actual, 2

                    B, FTE, 4

                ];

                 

                Summary:

                load *,

                if(Actual>FTE,'OVERSTAFFED','UNDERSTAFFED') as Status

                ;

                load

                Team,

                Sum(ActualNbr) as Actual,

                Sum(FTENbr) as FTE

                group by Team

                ;

                load

                Team,

                if(Type='Actual',Number,0) as ActualNbr,

                if(Type='FTE',Number,0) as FTENbr

                resident Data

                ;

                ////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

                It would give you an output like this.

                Capture.PNG