2 Replies Latest reply: Dec 17, 2012 8:22 AM by tylerjameswilson RSS

    Creating a variable field in the load script?

      Hello. I am trying to create a variable for the document that I can use all over, that checks several conditions within the fields I'm pulling in from a database.

       

      Let's call the variable a color for example, vColor.

       

      SQL Select Market,

                       Region,

                       Batman, etc.

               

       

       

      vColor can equal Red, Green, or Yellow.

       

      vColor is Red if the field Batman is Null and the field Region is Gotham.

      vColor is Yellow if the field Batman is Null and the field Region is London.

      vColor is Green if.....etc.

       

      And of course I would like to use this variable field in my sheet objects.

       

      I'm assuming I need to do this in the load statement, right? I don't understand how to give a variable three possible values and assign them according to outside values.

        • Re: Creating a variable field in the load script?

          Hi tylerjameswilson,

           

          it seems to me that you better create a new field during the load. A variable can have a lot different values during loadtime but only one "endvalue" when the load is done. So my suggestions is using a preceding load to create an additional field "MyColor" similar to:

           

          LOAD

               Market, Region, Batman . . . and the rest,

               if( Len(Trim(Batman)) = 0 and Region = 'Gotham', 'Red',     // isnull(Batman) and . ..  should work also

               if( Len(Trim(Batman)) = 0 and Region = 'London', 'Yellow',

               if( Len(Trim(Batman)) = 0 and Region = 'Paris', 'green',

               'unkown'

               )))     AS MyColor

               ;

          SQL Select Market . .. and the rest

          From Table;

           

          Now MyColor can be used everywhere (Listboxes etc.) in the whole app.

           

           

          HtH

          Roland