Adding multiple values to a field

[Anonymous]

Hi all,

I have two columns namely ZMERGE and SUBJECT GROUP. (table 1)

I have another table with ZMERGE values which refers to table 1 to give me subject group of the user: (table 2)

Table 1:

ZMERGE	SUBJECT GROUP
CH	Chemistry
LS	Life Sciences
MD	Medical Sciences
PS	Psychology

Table 2:

NAME	ZMERGE
John	CH
Peter	LS
Barry	MD
Frank	CH, LS, MD, PS

How do I, in the load script write in a value for SUBJECT GROUP call MULTIPLE_SUBJECTS so that for people like Frank (with multiple values in ZMERGE, they will have a proper value in SUBJECT GROUP?

(Also, the actual list is long so we can't just code in CH, LS, MD and PS).

Thank you in advance for all help!

Regards,

Wai Meng

[Anonymous · Accepted solution]

This will create seperate rows for each ZMERGE that a NAME has in Table 2.

Table2:

LOAD NAME,

     trim(subfield ( ZMERGE , ',' )) as ZMERGE

FROM

[https://community.qlik.com/thread/276662]

(html, codepage is 1252, embedded labels, table is @2);

From that yo should be able visualize it how yo wish.

[devarasu07]

Table1:

LOAD * INLINE [

    ZMERGE1, SUBJECT GROUP

    CH, Chemistry

    LS, Life Sciences

    MD, Medical Sciences

    PS, Psychology

];

Table2:

Load *,SubField(ZMERGE,',') as ZMERGE1;

LOAD * INLINE [

    NAME, ZMERGE

    John, CH

    Peter, LS

    Barry, MD

    Frank, "CH, LS, MD, PS"

];

drop Field ZMERGE from Table2;

[Anonymous]

Hi Bill,

That worked a treat! Thank you!

I'm just wondering though for people with a variety of multiple ZMERGE values, if i still want to have it labelled as MULTIPLE_SUBJECTS in SUBJECT GROUP, how do we do it?

[Anonymous]

Hi Devarasu,

Thank you for your help. However, I have a couple hundred of rows so I won't be able to list each row.

Regards,

Wai Meng

[devarasu07]

Hi,

Subfield function works for N of values. would be able to share mock app and expected result. will check and help you. thanks Deva

[Anonymous]

Try using a Measure expression like this in your viz :

concat ( [SUBJECT GROUP]  , ', ' )

Having just NAME as you Dimension

[sth_wch004]

!

[sumanta12]

TABLE1:

LOAD

ZMERGE,

    [SUBJECT GROUP]

FROM

[https://community.qlik.com/thread/276662]

(html, codepage is 1252, embedded labels, table is @1);

TABLE2:

LOAD

NAME,

TRIM(SubField(ZMERGE,',')) AS ZMERGE;

LOAD

NAME,

    ZMERGE

FROM

[https://community.qlik.com/thread/276662]

(html, codepage is 1252, embedded labels, table is @2);

[Anonymous]

Thank you for this.

My initial thought was to have multiple ZMERGE values named as MULTIPLE_SUBJECTS.

In the end, subfield does the job pretty well (even better actually) to have the different subjects attributed to Frank.

Thanks alot for all your help and suggestions!