Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

juleshartley
Not applicable

Fast retrieve of distinct values

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

Tags (2)
13 Replies
ali_hijazi
Not applicable

Re: Fast retrieve of distinct values

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

I can walk on water when it freezes
oknotsen
Not applicable

Re: Fast retrieve of distinct values

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.

May you live in interesting times!
juleshartley
Not applicable

Re: Fast retrieve of distinct values

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

oknotsen
Not applicable

Re: Fast retrieve of distinct values

How about this:

SomeIndependentTable:

LOAD DISTINCT

SomeFieldName

RESIDENT OriginalTable;

May you live in interesting times!
juleshartley
Not applicable

Re: Fast retrieve of distinct values

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

richard_chilvers
Not applicable

Re: Fast retrieve of distinct values

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

ali_hijazi
Not applicable

Re: Fast retrieve of distinct values

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

I can walk on water when it freezes
juleshartley
Not applicable

Re: Fast retrieve of distinct values

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.

ali_hijazi
Not applicable

Re: Fast retrieve of distinct values

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)

I can walk on water when it freezes