Show distinct value in Listbox

[#1]

Hello everyone,

I want to show in Listbox all the distinct values, for example the year number from a Date column.

So I want to have listbox like:

2012

2011

2010....

Not like a list of 10 times 2012 before getting to 2011.

Do you know how to set it up?

Thank you in advance.

[CELAMBARASAN]

Hi,

     List box always show only distinct values?

Celambarasan

[jagan]

Hi,

In Load script arrive a new column for year like below

LOAD

     Date,

     Year(Date) AS Year

FROM DataSource;

Now use Year field in List box.

Hope this helps you.

Regards,

Jagan.

[jonathandienst · Accepted solution]

Hi

Listboxes will only show distinct values. If you have multiple entries for 2012, then I suspect that these values are distinct dates formatted to show only the year. So they look like duplicates, but they are actually separate values.

To avoid this, create a Year field for the date you want to select, or use Year(date) as an expression in the list box.

Regards

Jonathan

[#5]

Jonathan and Jagan,

Yes, you're right. They are actually the distinct dates formatted to show only the year.

Your solutions worked. Thanks.

In the meanwhile I found another way to solve it without creating new field.

Add a Listbox with Expression: Trim(Year(Date)). It works the same.

Regards,

Chung

[jagan]

Hi,

Creating a New field is best option due to performance reason, if you do not get the Year field everytime the expression gets evaluated and creates the list box.  I suggest you creating the new Year field instead of using the expression.

Regards,

Jagan.

[#7]

Hi Jogan,

Thanks for your advice. I'm new with Qlikview, so it's useful to know this information.

Anyway, I've found one more disadvantage of the trim(...) solution. I can't create a trigger (for a default value) on that listbox.

Regards,

Chung