topic Re: Multivaluated cells in QlikView

Multivaluated cells

Mon, 08 Sep 2014 16:16:33 GMT

Hi Comm,

Does anbody happen to know to script load a multivaluated cell excel file ? cell values are separated by ' ;'

Thanks!

Re: Multivaluated cells

Mon, 08 Sep 2014 16:19:45 GMT

Hi,

Give me a some sample data and what/how exactly you want?

Vinay

Re: Multivaluated cells

Colin-Albert — Mon, 08 Sep 2014 16:25:18 GMT

Have a look at the subfield command, this can expand the data into individual rows for each value.

Re: Multivaluated cells

Mon, 08 Sep 2014 16:26:32 GMT

Project Contact Name

WARSAW Ana

WARSAW; ITALY John

ITALY Peter

Above is the excel I have, two columns.

Once I normal load into QVW and later I make a Project filter I have three Projects, but shold be only two.

Does it make sense to you?

Many thanks!

Re: Multivaluated cells

Colin-Albert — Mon, 08 Sep 2014 16:31:02 GMT

TRY THIS

LOAD

SUBFIELD(Project, ';') AS Project,

[Contact Name]

FROM

[PROJDATA.xlsx]

(ooxml, embedded labels, table is Sheet1);

Re: Multivaluated cells

Colin-Albert — Mon, 08 Sep 2014 16:32:35 GMT

Trim added below to remove extra spaces

LOAD

trim(SUBFIELD(Project, ';')) AS PROJECT,

[Contact Name]

FROM

(ooxml, embedded labels, table is Sheet1);

Re: Multivaluated cells

MarcoWedel — Mon, 08 Sep 2014 16:34:38 GMT

LOAD Subfield(Project, ';') as Project,

[Contact Name]

From yourdatasource;

should deliver :

Project Contact Name

WARSAW Ana

WARSAW John

ITALY John

ITALY Peter

hope this helps

regards

Marco

Re: Multivaluated cells

Mon, 08 Sep 2014 16:34:58 GMT

Thanks Colin, I’m trying now.

De: Colin Albert

Enviado el: lunes, 08 de septiembre de 2014 18:33

Para: Quintero, Borja @ Madrid

Asunto: Re: - Multivaluated cells

Qlik Community<http://community.qlik.com/>

Multivaluated cells

reply from Colin Albert<http://community.qlik.com/people/Colin_Albert?et=watches.email.thread> in Scripting - View the full discussion<http://community.qlik.com/message/604368?et=watches.email.thread#604368>

Re: Multivaluated cells

Mon, 08 Sep 2014 16:38:34 GMT

Hi Colin, negative…☹ did not work, as you see they are not trimmed…any other sugestion?

De: Colin Albert

Enviado el: lunes, 08 de septiembre de 2014 18:33

Para: Quintero, Borja @ Madrid

Asunto: Re: - Multivaluated cells

Qlik Community<http://community.qlik.com/>

Multivaluated cells

Re: Multivaluated cells

Mon, 08 Sep 2014 16:39:19 GMT

Hi,

Colin & Marco both correct you can use SUBFIELD.

Vinay

Re: Multivaluated cells

Mon, 08 Sep 2014 16:40:58 GMT

Sorry, does not work, see below…

ContactosOutLook:

LOAD trim(subfield(Categories, '; '))as Categories,

ContactosNombre,

ContactosOrganizacion,

ContactosDepartamento,

ContactosPuesto,

ContactosMobilePhone,

ContactosEmailAddress

FROM

De: Marco Wedel

Enviado el: lunes, 08 de septiembre de 2014 18:35

Para: Quintero, Borja @ Madrid

Asunto: Re: - Multivaluated cells

Qlik Community<http://community.qlik.com/>

Multivaluated cells

reply from Marco Wedel<http://community.qlik.com/people/MarcoWedel?et=watches.email.thread> in Scripting - View the full discussion<http://community.qlik.com/message/604370?et=watches.email.thread#604370>

Re: Multivaluated cells

Colin-Albert — Mon, 08 Sep 2014 16:43:03 GMT

Is there a space after the semicolon in the subfield command, can you try it without the space.

';' rather than '; '

Re: Multivaluated cells

Mon, 08 Sep 2014 16:43:23 GMT

Hi Vinay, It does not work, see below…any suggestions?

ContactosOutLook:

LOAD trim(subfield(Categories, '; '))as Categories,

ContactosNombre,

ContactosOrganizacion,

ContactosDepartamento,

ContactosPuesto,

ContactosMobilePhone,

ContactosEmailAddress

FROM

De: Vinay Naran

Enviado el: lunes, 08 de septiembre de 2014 18:40

Para: Quintero, Borja @ Madrid

Asunto: Re: - Multivaluated cells

Qlik Community<http://community.qlik.com/>

Multivaluated cells

reply from Vinay Naran<http://community.qlik.com/people/VINAYNARAN?et=watches.email.thread> in Scripting - View the full discussion<http://community.qlik.com/message/604373?et=watches.email.thread#604373>

Re: Multivaluated cells

Mon, 08 Sep 2014 16:45:47 GMT

mmm…I have tried but same problem..albeit subfield should work as I’m looking into community…not a clue why is not working but still have projects which name is a mixture of projects instead of being one by file-

De: Colin Albert

Enviado el: lunes, 08 de septiembre de 2014 18:43

Para: Quintero, Borja @ Madrid

Asunto: Re: - Multivaluated cells

Qlik Community<http://community.qlik.com/>

Multivaluated cells

Re: Multivaluated cells

Colin-Albert — Mon, 08 Sep 2014 16:51:35 GMT

Does your spreadsheet load include wildcards? Some functions do not work when loading using wildcards.

Or do you have qualify enabled?

Try loading from your spreadsheet into a temp table and then doing a resident load to apply the subfield command.

tmpProjects:

LOAD

Project,

[Contact Name]

FROM

(ooxml, embedded labels, table is Sheet1);

Projects:

NoConcatenate

load

trim(SUBFIELD(Project, ';')) AS Project,

[Contact Name]

resident tmpProjects ;

drop table tmpProjects;

Re: Multivaluated cells

Mon, 08 Sep 2014 16:53:36 GMT

its working...

see below screenshot

Re: Multivaluated cells

Mon, 08 Sep 2014 16:59:35 GMT

Right! It works ☺ I was having data waste and it was confusing me. But now it works.

Many thanks.

BQ.

De: Vinay Naran

Enviado el: lunes, 08 de septiembre de 2014 18:54

Para: Quintero, Borja @ Madrid

Asunto: Re: - Multivaluated cells

Qlik Community<http://community.qlik.com/>

Multivaluated cells

Re: Multivaluated cells

Mon, 08 Sep 2014 17:02:35 GMT

Hi Colin ,not necessary, I Works perfectly. How can I tip points?

Best,

BQ.

De: Colin Albert

Enviado el: lunes, 08 de septiembre de 2014 18:52

Para: Quintero, Borja @ Madrid

Asunto: Re: - Multivaluated cells

Qlik Community<http://community.qlik.com/>

Multivaluated cells

Re: Multivaluated cells

MarcoWedel — Mon, 08 Sep 2014 18:42:06 GMT

Hi,

just select correct answer and/or helpful in your thread at the posts that helped you or solved your problems..

thanks

regards

Marco