topic Re: Splitting comma separated value stored in variable so I can use it is a dimension in QlikView

Splitting comma separated value stored in variable so I can use it is a dimension

jjordaan — Sat, 23 Feb 2013 09:39:38 GMT

Hello QlikViewers,

I have the following issue.

I have a comma separated string that is stored in a variable. The string that is stored in the variable looks like this '16', '17a', '18', '19-C', '20', '92'.

I need to find a way that a can split these values so that I can use those values as a dimension.

I tried to use the function SubField but I retrieve only on value and I need them all.

The Straight table or Pivot table I want to create is like this

House No. Number of Persons

16 4

17a 1

18 6

19-C 2

20 3

92 4

Thanks in advance

Re: Splitting comma separated value stored in variable so I can use it is a dimension

SunilChauhan — Sat, 23 Feb 2013 10:14:51 GMT

Can you please tell me how you stored in variable.

is it stored without syntax error.

Sunil

Re: Splitting comma separated value stored in variable so I can use it is a dimension

whiteline — Sat, 23 Feb 2013 10:44:34 GMT

Hi.

You can use SubField function without third parameter in load script, to get all values separately.

What is the Number of Persons ?

Re: Splitting comma separated value stored in variable so I can use it is a dimension

jjordaan — Sat, 23 Feb 2013 13:47:10 GMT

Hi Luminary,

Maybe I should explain i bit more what my situation is.

Ik have created an Excel file where the end user can set some settings.

For example one the settings is which container area's should be marked as an private area.

So the Excel file looks like this

Setup Value

Area 16, 17a, 18, 19-C, 20, 92

In my load script I'm using a function to put those values into a variable.

These variables a use a lot in Set Analysis.

I hope I could answer tour question

Re: Splitting comma separated value stored in variable so I can use it is a dimension

jjordaan — Sat, 23 Feb 2013 13:51:57 GMT

Hi Whiteline,

The values came from an Excel file and in my load script I put these values in a variable.

Setup:

LOAD

ID AS IKEASetupID,

Setup AS Setup,

Chr(39) & Replace(Replace(Value,' ','')

,',',Chr(39) &','&Chr(39)) & Chr(39) AS SetupValue

From

$(vControlFileScriptLocation)

(ooxml, embedded labels, table is Setup);

LET vContainerArea = Peek('SetupValue', 0, 'Setup');

The variables I create in the above example are used very much in my calculations with Set Analysis.

The number of persons in mij example are the number of persons who has Access to that Area (House No).

Thanks for your help

Re: Splitting comma separated value stored in variable so I can use it is a dimension

jjordaan — Sat, 23 Feb 2013 13:53:46 GMT

I forgot to say that I hope there is a way to separate these values in my dashboard and not in script.

Re: Splitting comma separated value stored in variable so I can use it is a dimension

whiteline — Sat, 23 Feb 2013 15:35:20 GMT

If the value of vContainerArea look like '16', '17a', '18', '19-C', '20', '92' then you could create a dimension from it with ValueList:

=ValueList($(vContainerArea))

Re: Splitting comma separated value stored in variable so I can use it is a dimension

jjordaan — Sat, 23 Feb 2013 16:00:22 GMT

Whiteline,

Thanks for your help.

I have a quetion about your answer.

You said that if the value looks like '16', '17a' etc I could use ValueList.

But if the values are without the quotes? Can't I use the function ValueList?

Thanks again for your help

Re: Splitting comma separated value stored in variable so I can use it is a dimension

whiteline — Sat, 23 Feb 2013 20:28:20 GMT

No, you can't.

QlikView just substitutes '16', '17a' instead of $(vContainerArea) so that it becomes =ValueList('16', '17a')

It's incorrect syntax without the quotes.

I think you could use subfield function to split the string without the quotes directly in script:

LOAD

Subfield(Peek('SetupValue', 0, 'Setup'), ',') as fContainerArea

AUTOGENERATE (1);