topic Re: How do I show only those fields in list box whose data is present in qlikview? in QlikView

How do I show only those fields in list box whose data is present in qlikview?

Tue, 20 Oct 2015 06:21:44 GMT

I have a dimension YEAR which shows values from 2008 to 2015. However the data is available only from 2011-2015. How do I not display those extra values.?

Re: How do I show only those fields in list box whose data is present in qlikview?

oknotsen — Tue, 20 Oct 2015 06:39:24 GMT

Qlik can only show data that is actually in the application, so apparently you DO have data for 2008, 2009 and 2010.

If this is the result of a Master Calendar being created from a too early date, adjust that in your script.

Re: How do I show only those fields in list box whose data is present in qlikview?

Tue, 20 Oct 2015 06:41:44 GMT

From 2008 to 2010 it is greyed out. I want to remove the grey values. How should I proceed?

Re: How do I show only those fields in list box whose data is present in qlikview?

avinashelite — Tue, 20 Oct 2015 06:42:03 GMT

I think your link the date fields from two table that's why you getting additional dates.

You can add a expression in the list box like if(YEAR>='2011',YEAR)

or you can reduce the data in the script level by applying the where conditions

create a extra field in the table where you need to refer the YEAR

Re: How do I show only those fields in list box whose data is present in qlikview?

avinashelite — Tue, 20 Oct 2015 06:56:45 GMT

go to>list box>in the dimension list > at last you have option called expression>add this

if(YEAR>='2011',YEAR)

Re: How do I show only those fields in list box whose data is present in qlikview?

raajaswin — Tue, 20 Oct 2015 10:24:20 GMT

Try Hide excluded option

Re: How do I show only those fields in list box whose data is present in qlikview?

Tue, 20 Oct 2015 10:26:44 GMT

Doesn't work Avinash! It creates an extra column in listbox!

Re: How do I show only those fields in list box whose data is present in qlikview?

Tue, 20 Oct 2015 10:29:40 GMT

With hide excluded it just hides the values....I mean the grey area still stays!

Re: How do I show only those fields in list box whose data is present in qlikview?

raajaswin — Tue, 20 Oct 2015 13:05:46 GMT

Hi Harshit,

What avinash said is correct only. You must have added the expression under expressions tab. Instead give it in the general tab itself.. see the below picture

now type the expression here and press ok.

Expression to be given: =if(YEAR>='2011',YEAR)

Hope it helps!

Regards

Aswin

Re: How do I show only those fields in list box whose data is present in qlikview?

avinashelite — Wed, 21 Oct 2015 04:59:04 GMT

try like this :

if(YEAR>='2011',YEAR,null())

Re: How do I show only those fields in list box whose data is present in qlikview?

tyagishaila — Wed, 21 Oct 2015 05:49:29 GMT

Hi Harshit,

I think your YEAR field is present in two different tables. Like,

Table1 has value 2008-2015

and Table2 has 2011-2015.

updateTable2 script,

YEAR,

YEAR as NewYear

From ......

and Show NewYear field in listbox.

Re: How do I show only those fields in list box whose data is present in qlikview?

Tue, 27 Oct 2015 06:35:32 GMT

At backend I've the field YEAR whose values are 2008,09,10,11,12,13,14,15 but there are not any corresponding values for the years 2008,09,10,11.....so I have these values displayed in listbox but since I've no data for it...I don't want to display them....what should I do?

Re: How do I show only those fields in list box whose data is present in qlikview?

oknotsen — Tue, 27 Oct 2015 16:28:51 GMT

So the solution is to not load those years.

Without knowing the data model and the script in which you currently load the years I can't give you an exact solution on how to avoid those years being loaded.

Re: How do I show only those fields in list box whose data is present in qlikview?

tyagishaila — Wed, 28 Oct 2015 08:05:30 GMT

either use where condition in script,

or use if condition in list box.

Re: How do I show only those fields in list box whose data is present in qlikview?

jonathandienst — Wed, 28 Oct 2015 08:12:29 GMT

I assume the other dates are coming from your calendar. When you add the calendar, you could do it with like this:

Calendar:

Left Keep (FactTable)

LOAD Date As FactDate

...

FROM Calendar.qvd (qvd);

Modify the logic and field/table names to suit your requirement.

This will prevent the loading of calendar data for dates that do not exist in your fact table. And you don't need to mess around with set expressions or conditional list boxes...

Re: How do I show only those fields in list box whose data is present in qlikview?

Wed, 28 Oct 2015 11:37:57 GMT

Yup! It works! Thanks