Fast retrieve of distinct values

[juleshartley]

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

[swuehl]

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()));

[juleshartley]

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...

[ali_hijazi]

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

[juleshartley]

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...