topic Re: Urgent - Creating a listbox by combining unique values from different fields in QlikView

Urgent - Creating a listbox by combining unique values from different fields

sayadutt — Tue, 20 May 2014 14:51:12 GMT

Hi,

I have 3 fields - which contain some name of persons.

Now i need to create a single ListBox which will join these 3 fields and show me the unique values (there may be same values in 2 fields)

Can you please suggest how i can do this.

Thanks

Re: Urgent - Creating a listbox by combining unique values from different fields

alexandros17 — Tue, 20 May 2014 14:56:43 GMT

You must do this in the script ...

Re: Urgent - Creating a listbox by combining unique values from different fields

sayadutt — Tue, 20 May 2014 15:08:48 GMT

Can you please let me know the syntax, i am very new to QV

Re: Urgent - Creating a listbox by combining unique values from different fields

alexandros17 — Tue, 20 May 2014 15:11:35 GMT

Send me your document and tell me the fields you want

Re: Urgent - Creating a listbox by combining unique values from different fields

MayilVahanan — Tue, 20 May 2014 15:12:22 GMT

Give the same name for all three fields.

Ex:

Load fielda as field1 from table1;

Load fieldb as field1 from table2;

Load fieldc as field1 from table3;

Then reload the file. And use that field1 in listbox.

Hope it helps

Re: Urgent - Creating a listbox by combining unique values from different fields

Tue, 20 May 2014 15:15:15 GMT

within your load script concatenate all the fields your are looking to see it as a single field.

ex: field1&'-'&field2&'-'&field3

Re: Urgent - Creating a listbox by combining unique values from different fields

sayadutt — Tue, 20 May 2014 15:24:16 GMT

Hi,

Currently, I am loading data from SharePoint which has 1 cell containing 3 values.

Eg: 1 project assigned to 3 person (these 3 person names are given in 1 cell, separated by semicolon)

In order to filter on each person name, i am using the below code to split it into 3 fields. (for internal use)

But in the search box i need to search my person name only, thats why i need to generate 1 list.

Can you please let me know where should I add the code you provided.

tab:

LOAD

[Title ],

[Associates Working on ]

FROM

[sharepoint list path]

(html, unicode, embedded labels, table is @4);

for i=1 to 3

Tab23:

LOAD *, SubField([Associates Working on ],';',$(i)) as fieldname$(i)

Resident tab ;

next i;

drop table tab;

Re: Urgent - Creating a listbox by combining unique values from different fields

sayadutt — Tue, 20 May 2014 15:25:03 GMT

Hi,

Currently, I am loading data from SharePoint which has 1 cell containing 3 values.

Eg: 1 project assigned to 3 person (these 3 person names are given in 1 cell, separated by semicolon)

In order to filter on each person name, i am using the below code to split it into 3 fields. (for internal use)

But in the search box i need to search my person name only, thats why i need to generate 1 list.

tab:

LOAD

[Title ],

[Associates Working on ]

FROM

[sharepoint list path]

(html, unicode, embedded labels, table is @4);

for i=1 to 3

Tab23:

LOAD *, SubField([Associates Working on ],';',$(i)) as fieldname$(i)

Resident tab ;

next i;

drop table tab;

Re: Urgent - Creating a listbox by combining unique values from different fields

Peter_Cammaert — Tue, 20 May 2014 15:50:23 GMT

You can use the 2-parameter version of SubField(). It will split multiple values in a single cell into different rows, each containing a single value, like:

Tab23:

NOCONCATENATE

LOAD Title, SubField([Associates Working on ],';') as [Single Associate Working On]

RESIDENT tab ;

You end up with a single field containing all values. Now in the UI, apply whatever count() function you want.

Best,

Peter