Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
Thanks Roland. I think I was asking my question poorly but you figured it out. Works great, thanks again!