Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

List Box Options

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

NameCityStatePhone
Rober DeNiroHollywoodCA
Will SmithNew YorkNY
John DoeXXX-YYZ-ZZZZ
Jane DoeZZZ-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?

5 Replies

Re: List Box Options

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

montero91
Contributor

Re: List Box Options

Hi AndrePeter,

Create a new field within the load

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

or

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

nagaiank
Valued Contributor III

Re: List Box Options

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.

Not applicable

Re: List Box Options

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

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

Community Browser