topic List Box List Separator Question in QlikView

List Box List Separator Question

Fri, 16 Sep 2011 09:42:42 GMT

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.

List Box List Separator Question

gandalfgray — Fri, 16 Sep 2011 10:00:57 GMT

Hi Eric

concat is not the right function for you to use.

concat makes one record out of many records

Example

(sourcedata:)

Table1

Field1

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

List Box List Separator Question

Fri, 16 Sep 2011 10:11:47 GMT

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.

Re: List Box List Separator Question

Miguel_Angel_Baeyens — Fri, 16 Sep 2011 10:19:51 GMT

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

List Box List Separator Question

kaushiknsolanki — Fri, 16 Sep 2011 10:24:26 GMT

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

List Box List Separator Question

Fri, 16 Sep 2011 10:25:22 GMT

Hi Miguel, Kaushik & gg,

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

Eric.

List Box List Separator Question

Fri, 16 Sep 2011 11:05:16 GMT

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.

Re: List Box List Separator Question

Miguel_Angel_Baeyens — Fri, 16 Sep 2011 11:21:51 GMT

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

Re: List Box List Separator Question

Fri, 16 Sep 2011 12:02:25 GMT

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.