Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone,
I am stucked on the issue when I want to use Concat function to populate a drop down list box from a field in a table which contain comma. Unfortunately, in my environment, the ListSep is set to ",". Is there anyway to set ListSep to something else or escape the charater in the data?
Many thanks in advance,
Eric.
Hi Eric,
The test we did in that post led us to the conclusion that the separator that works is the Doc one, not the Sys one. Meaning that if your Doc (local) sep is "," and mine is ";" I still will see your document fine, and viceversa, but when I create a new document, using "," will not work. So set the sList value to "," (or any other value) and make sure that the Control Panel is reading that info (in Windows 7 go to Control Panel, Regional and Language, Advanced, Number tab, Lists separator, and make sure that the dropdown selects the one you want to use.
Then using Concat() with that separator should work if that doesn't conflict with some other separator (in my case, Spanish list separator is ";" because the decimal separator is "," and might lead to confusion.
Hope that helps.
BI Consultant
Hi Eric
concat is not the right function for you to use.
concat makes one record out of many records
Example
(sourcedata:)
Table1
Field1
A
B
C
Table2
Load concat(Field1,' + ') As concatField
Resident Table1;
would give this in Table2:
Table2
concatField
A + B + C
what data do you have to start with
/gg
Hi gg,
Thanks for you reply. The concat I use is in the list box not in the load script.
My Table is
DupAnalysis:
LOAD * Inline
[AnalysisDisplayName, AnalysisFields
'Dup1: Supplier Name', VendorNameCount
'Dup2: Supplier Name, Address Line 1, Post Code', VendorNameAddress1PostCodeCount
'Dup3: Address Line 1, Post Code', Address1PostCodeCount
'Dup4: Post Code', PostCodeCount
];
In the List Box Properties > Constraints > Listed Values:
=concat(distinct AnalysisDisplayName,',')
In my Document Support Information:
ListSep Doc=","(2C) Sys=","(2C)
Thanks,
Eric.
Hi Eric,
Listboxes don't show values comma separated (or using any other separator). Inputboxes do. You may find this idea and the post I mention within helpful.
So are the values showing as one string comma separated or does the drop down work now?
BI Consultant
HI,
I think Eric wanted to write Inputbox but he wrote Listbox. Anyways.
Have a look at the below post where Miguel had given the solution.
http://community.qlik.com/message/143350#143350
Regards,
Kaushik Solanki
Hi Miguel, Kaushik & gg,
Kaushik, you are right. My mistake. I have put List Box instead of Input Box.
Eric.
Hi Miguel and Kaushik,
Thanks for your suggestions. I have changed the registry entry to Computer > HKEY_CURRENT_USER > International > sList = ";" and reboot my computer.
The Document Support Information is now as follows:
ListSep Doc=","(2C) Sys=";"(3B)
Change the input box properties
Unfortunately, the input box is still not quite work.
Eric.
Hi Eric,
The test we did in that post led us to the conclusion that the separator that works is the Doc one, not the Sys one. Meaning that if your Doc (local) sep is "," and mine is ";" I still will see your document fine, and viceversa, but when I create a new document, using "," will not work. So set the sList value to "," (or any other value) and make sure that the Control Panel is reading that info (in Windows 7 go to Control Panel, Regional and Language, Advanced, Number tab, Lists separator, and make sure that the dropdown selects the one you want to use.
Then using Concat() with that separator should work if that doesn't conflict with some other separator (in my case, Spanish list separator is ";" because the decimal separator is "," and might lead to confusion.
Hope that helps.
BI Consultant
Hi Miguel,
Yes, you are right. The change in the registry or control panel only take effect on the Doc setting on document that created after the change. The old document Doc will not change except the Sys one. In my case, which I need to change the Doc setting and Sys one is no use.
Thanks,
Eric.