Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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))
Can you please tell me how you stored in variable.
is it stored without syntax error.
Sunil
Hi.
You can use SubField function without third parameter in load script, to get all values separately.
What is the Number of Persons ?
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
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
I forgot to say that I hope there is a way to separate these values in my dashboard and not in script.
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))
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
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);