List Box List Separator Question

[#1]

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.

[gandalfgray]

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

[#3]

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.

[Miguel_Angel_Baeyens]

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?

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[kaushiknsolanki]

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

[#6]

Hi Miguel, Kaushik & gg,

Kaushik, you are right.  My mistake.  I have put List Box instead of Input Box.

Eric.

[#7]

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.

[Miguel_Angel_Baeyens · Accepted solution]

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.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[#9]

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.