topic Re: Fast retrieve of distinct values in QlikView

Fast retrieve of distinct values

juleshartley — Wed, 15 Apr 2015 08:21:29 GMT

hi All,

I'm throwing this one out there for discussion. I have a feeling that I've seen it discussed before, but can't find the thread!

Regularly I will have a large table that I will then need to retrieve distinct values of dimensional fields from in order to display in selection boxes etc. This obviously impacts the load significantly as it has to load the full table each time.

Any ideas on ways to improve this process? (Flags, group by's etc.)

Thanks!

ps. As pointed out below - the reason for doing this is that I need a separate table that is not linked to the main table

Re: Fast retrieve of distinct values

ali_hijazi — Wed, 15 Apr 2015 08:23:34 GMT

The list box actually displays distinct values of any column, so why do you have to load distinct values ?

Re: Fast retrieve of distinct values

oknotsen — Wed, 15 Apr 2015 08:27:43 GMT

A List Box shows the values distinct by default.

Since you probably already knew that, my guess is your actual problem is a bit different. Please explain your actual problem / challenge.

Re: Fast retrieve of distinct values

juleshartley — Wed, 15 Apr 2015 08:31:46 GMT

I need it to be an independent dataset (ie. not linked to the 'main' table)

Re: Fast retrieve of distinct values

oknotsen — Wed, 15 Apr 2015 08:42:40 GMT

How about this:

SomeIndependentTable:

LOAD DISTINCT

SomeFieldName

RESIDENT OriginalTable;

Re: Fast retrieve of distinct values

juleshartley — Wed, 15 Apr 2015 08:45:53 GMT

Thanks OvK, that's the technique that I've been using but was hoping for any better performing alternatives...

Re: Fast retrieve of distinct values

richard_chilvers — Wed, 15 Apr 2015 08:50:23 GMT

Hi Julian

Its an interesting topic, and I have a similar issue. It does seem strange that a load from a large table is slow even when the resulting data is minimal. I hope there will be some further comments.

regards

Re: Fast retrieve of distinct values

ali_hijazi — Wed, 15 Apr 2015 09:12:10 GMT

then use alternate states if you want to have "in a different dataset"

Re: Fast retrieve of distinct values

juleshartley — Wed, 15 Apr 2015 09:16:40 GMT

Not that straightforward I'm afraid... I need to, for example, have date selections to apply to the start of a waterfall chart and the end of the same waterfall chart.

Re: Fast retrieve of distinct values

ali_hijazi — Wed, 15 Apr 2015 09:18:10 GMT

then use Claendar control and link them to 2 variables then you expression will have in the where condition somthing like this:

sum({<Date={">=$(fromDate) <=$(ToDate)"}>} Metric)

Re: Fast retrieve of distinct values

swuehl — Wed, 15 Apr 2015 09:26:24 GMT

Coming back to your original request, to retrieve the distinct values of an already loaded (resident) table, try something like:

DistinctValuesTable:

LOAD fieldvalue('FIELD',iterno()) as DistinctFieldValues

AUTOGENERATE 1

WHILE len(fieldvalue('FIELD',iterno()));

Re: Fast retrieve of distinct values

juleshartley — Wed, 15 Apr 2015 09:28:06 GMT

Sorry AH, but you're trying to answer a particular situation rather than my actual question. Just take it as a given that I need to do a fast filter of one table into a separate table of distinct values... for whatever reason. And that is what I was interested in discussing...

Re: Fast retrieve of distinct values

ali_hijazi — Wed, 15 Apr 2015 09:39:53 GMT

use alternate states and get the selected values using concat({your_desire_state} distinct your_column,',')

Re: Fast retrieve of distinct values

juleshartley — Wed, 15 Apr 2015 09:44:58 GMT

Thanks swuehl, I've seen this in relation to building a calendar and it was very effective. Will give it a go and report back!

I guess it can only work for one field in the table, rather than multiple fields...