Creating a variable field in the load script?

[#1]

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.

[#2 · Accepted solution]

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

[#3]

Thanks Roland. I think I was asking my question poorly but you figured it out. Works great, thanks again!