topic Re: Creating a variable field in the load script? in QlikView

Creating a variable field in the load script?

Thu, 13 Dec 2012 17:53:05 GMT

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?

Sun, 16 Dec 2012 16:24:37 GMT

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

Re: Creating a variable field in the load script?

Mon, 17 Dec 2012 13:22:18 GMT

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