topic Re: List Box Options in QlikView

List Box Options

Wed, 18 Jul 2012 21:59:51 GMT

COnsider I have below table in which City and State field is marked as blank.

Name	City	State	Phone
Rober DeNiro	Hollywood	CA
Will Smith	New York	NY
John Doe			XXX-YYZ-ZZZZ
Jane Doe			ZZZ-XXY-YYYY

Now if I created list box with state names, it will just show me CA and NY values. Is there any way to show in list box theer are some blank values in this field?

Re: List Box Options

MayilVahanan — Wed, 18 Jul 2012 22:08:31 GMT

Hi,

Try like this,

Load *,if(Len(Trim(State)) >0, State,'N/A') as State from tablename;

(or)

Load *,if(Len(Trim(State)) >0, State,Null()) as State from tableName;

Hope it helps

Re: List Box Options

montero91 — Wed, 18 Jul 2012 22:19:52 GMT

Hi AndrePeter,

Create a new field within the load

If(State=null(), ’Blank’ , State) as State_Global

If(State='' , ’Blank’ , State) as State_Global

Re: List Box Options

nagaiank — Wed, 18 Jul 2012 23:15:39 GMT

In your load script, before loading the data, add the following statement:

NullAsValue City, State;

Then create listboxes for the fields City and State. You will see a blank line in the listbox.

Hope this helps.

Re: List Box Options

Thu, 19 Jul 2012 15:30:47 GMT

Thanks for all the responses. It was helpful. But there is small isssue now. Sorry i should have made my issue very clear. I am creating this table using join of two tables.

TableA

[

Name,

City,

Phone

]

TableB

[

City,

State

]

Then on dashboard I show view of combined table shown in my proginal post. I tried below three options-

A) I created user defined variable on dashboard State_Val=(if len(State) = 0, 'Blank',State)

And tried to use this variable in Listbox, this didn't work.

B) Later I tried to use left join between Table A and Table B, here i tried to add extra vaiable "(if len(State) = 0, 'Blank',State) As State_Val). And later I tried to use this variable in listbox, it didn't work.

C) Later I tried to use left join between Table A and Table B, here i tried to add extra vaiable "(if len(State) = null() 'Blank',State) As State_Val). And later I tried to use this variable in listbox, it didn't work either.

Re: List Box Options

MayilVahanan — Thu, 19 Jul 2012 20:16:45 GMT

HI,

After join both table like this,

TableA

Load

Name,

City,

Phone

from TableA;

Temp;

join(TableA)

Load

City,

State

from tableB;

Load * , if(len(trim(State)) > 0, State,'Blank') as State resident TableA;

Drop table Temp;

Then use , State field in listbox