topic Re: Un-concat'ing in QlikView

Un-concat'ing

Thu, 23 Jan 2014 14:47:18 GMT

Hi,

I want to create a list box that separates out a field that has comma's as separators.

i.e

Default List Box Values:

Asia, BAU, Regions

Asia, Project, Regions

BAU, Ops

Ops, Project

CD, Project

What I want is the same values, but separately selectable:

Asia

BAU

Project

Regions

Ops

I'm happy to hardcode the list somewhere and to be able to filter on any record that has one or more of the selected values.

Any help would be appreciated.

Thanks

Simon

Re: Un-concat'ing

tresB — Thu, 23 Jan 2014 14:50:30 GMT

Use Subfield() function. Like: Subfield(Fieldname, ',' , 1)

Re: Un-concat'ing

ThornOfCrowns — Thu, 23 Jan 2014 14:52:13 GMT

If you're happy to "hardcode" the list, make an Inline Table and use that, perhaps?

Re: Un-concat'ing

rustyfishbones — Thu, 23 Jan 2014 14:53:21 GMT

try using the SUBFIELD()

something like

Re: Un-concat'ing

Peter_Cammaert — Thu, 23 Jan 2014 14:53:22 GMT

You'ld better do this in your load script because a list box has to refer to some field in your data model.

The SUBFIELD() function has a 2-parameter function that does exactly what you want. It creates a single record for every subfield separated by a specific delimiter.

For instance:

LOAD subfield(ListBoxField, ',') AS SingleValue

RESIDENT ListBoxFieldTable;

will serialize alle values from your example into a list of individual records. A subsequent list box will skip the doubles.

Peter

Re: Re: Un-concat'ing

rustyfishbones — Thu, 23 Jan 2014 14:56:00 GMT

Hi,

Please see the attached file, I hope it helps

Re: Re: Un-concat'ing

Thu, 23 Jan 2014 15:01:51 GMT

Brilliant, thanks all! Works a treat.