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

[jjordaan]

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

[SunilChauhan]

Can you please tell me how you stored in variable.

is it stored without syntax error.

Sunil

[whiteline]

Hi.

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

What is the Number of Persons ?

[jjordaan]

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

[jjordaan]

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

[jjordaan]

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

[whiteline · Accepted solution]

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

[jjordaan]

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

[whiteline]

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