Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
MayilVahanan

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

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
montero91
Creator
Creator

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
Specialist III
Specialist III

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
Author

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.

MayilVahanan

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

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.