topic Listbox in QlikView

Listbox

Thu, 26 Jun 2014 07:17:15 GMT

Hello Experts

I have a listbox which shows all the values that are in my table exept for empty field. Is it possible to show the empty fields in the listbox, too? I would like to select the datasets that contain no values in this table through the listbox...

Thank you for your help!

Re: Listbox

Thu, 26 Jun 2014 07:20:06 GMT

Hi Heinrich,

Unfortunataly not. You will have to assign a "value" to the blanks inside the script to be able to select them.

For instance assign the word "NULL" to all the nulls and blanks and then you will be able to select them.

Regards,
Marius

Re: Listbox

alexandros17 — Thu, 26 Jun 2014 07:22:44 GMT

in your list box at the end of the listed fields there is an item "Expression", click and write

=If(IsNull(myfield) or Trim(myfield)='','---', myfield)

Re: Listbox

Anonymous — Thu, 26 Jun 2014 07:23:08 GMT

Hi,

You cannot show NULL value in the list object. You need to cast them as a "NULL" or "NA" to show in the list box. Below script may help you to achieve this.

NullAsValue FieldName;

Set NullValue='NA'

Re: Listbox

Thu, 26 Jun 2014 07:32:00 GMT

in you script you have to wirte:

if(isnull(myfield)=-1,'-',myfield) as FieldName

that means if you field is null you will set the value - and otherwise the regular value. if you do this you will find the - in your listbox.

regards,

Re: Listbox

Thu, 26 Jun 2014 07:34:55 GMT

Hi Satyadev

Thank you for your help

Do I have to connect it to a load statement? Like this? (The values in the table I am interested in are in 'Jobkriterium')

load *, NullAsValue Jobkriterium;

Set NullValue='NA'

SQL SELECT bez as Jobkriterium,

beznr as Jobkritnr,

bezart

FROM EASY.divbez where bezart = 9;

Best

Jan

Re: Listbox

Thu, 26 Jun 2014 07:38:35 GMT

Hi Magdalena

Thank you for your help

I tried the following:

load *, if(isnull(Jobkriterium)=-1,'-',Jobkriterium) as Jobkriterium;

SQL SELECT bez as Jobkriterium,

beznr as Jobkritnr,

bezart

FROM EASY.divbez where bezart = 9;

This did not work for me...

Re: Listbox

Thu, 26 Jun 2014 07:44:03 GMT

maybe first load your table from the database and then try loading the table again and use the if is send you

regards,

Re: Listbox

Thu, 26 Jun 2014 07:48:03 GMT

you also have to make sure that the field is null and not empty. 😉

regards,

Re: Listbox

aveeeeeee7en — Thu, 26 Jun 2014 07:48:45 GMT

Hi Jan

Use below if condition in your script:

if(len(Trim(YOUR_FIELD_NAME))>1,YOUR_FIELD_NAME,'BLANKS') AS YOUR_FIELD_NAME

and Reload the Application. Use YOUR_FIELD_NAME List Box, there you'll see the List Box with BLANKS as values.

Hope this will work for you.

Regards

Aviral

Re: Listbox

Anonymous — Thu, 26 Jun 2014 07:52:47 GMT

Hi Jan,

You have to write the script outside of the load statement,

NullAsValue Jobkriterium;

Set NullValue='NA';

load *;

SQL SELECT bez as Jobkriterium,

beznr as Jobkritnr,

bezart

FROM EASY.divbez where bezart = 9;

Re: Listbox

Thu, 26 Jun 2014 07:58:44 GMT

Cool! This one works, exept that it does not give me "NA" values.

Re: Listbox

Thu, 26 Jun 2014 08:01:47 GMT

Hi Allesandro

Thank your for your help.

I am not sure if I enter the code at the right lace since it duplicates my values in the listbox. Do I enter it under the header 'formula'?

Best

Jan

Re: Re: Listbox

Anonymous — Thu, 26 Jun 2014 08:11:16 GMT

It means, specified field doesn't have NULL value. Attachment for your reference.

Re: Listbox

Thu, 26 Jun 2014 08:16:23 GMT

Ok. If I read the fields that are empty into a table it shows '-' what else could be in these fields? 0?

Sorry for keeping you busy on this...

Re: Listbox

alexandros17 — Thu, 26 Jun 2014 08:22:12 GMT

In general tab of the listbox, in the "Field" at the bottom you find expression. Do not use the tab expression otherways as you said you have duplicated values

Re: Re: Listbox

Anonymous — Thu, 26 Jun 2014 08:22:56 GMT

If it is NULL, it will show '-' in tablebox. If you are using field in expression, for example Sales field has some null entry and if you use Sum(Sales) than it will give you 0.

Re: Listbox

Thu, 26 Jun 2014 08:32:14 GMT

Hi,

you need to assign the value for null to select them

just use

NullAsValue *;

SET NullValue ="NA";

tablename:

Load

....

...

;

Re: Listbox

Anonymous — Thu, 26 Jun 2014 08:46:06 GMT

Hi,

Use Nullasvalue concept and you will get the required output in the listbox

HTH

Ravi N.