topic Show distinct value in Listbox in QlikView

Show distinct value in Listbox

Fri, 30 Mar 2012 13:45:32 GMT

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.

Show distinct value in Listbox

CELAMBARASAN — Fri, 30 Mar 2012 13:48:34 GMT

Hi,

List box always show only distinct values?

Celambarasan

Show distinct value in Listbox

jagan — Fri, 30 Mar 2012 13:48:57 GMT

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.

Show distinct value in Listbox

jonathandienst — Fri, 30 Mar 2012 13:50:39 GMT

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

Show distinct value in Listbox

Fri, 30 Mar 2012 13:59:21 GMT

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

Show distinct value in Listbox

jagan — Fri, 30 Mar 2012 14:03:30 GMT

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.

Show distinct value in Listbox

Fri, 30 Mar 2012 14:12:08 GMT

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