How to Merge the contents of multiple columns and creating a new Column

[Anonymous]

Hello All,

I am trying to merge the contents of multiple columns and create a new field.

Eg:

Assignee1	Assignee2	Assignee3	Assignee4	Assignee5	Assignee6	Assignee7	Assignee8	Assignee9
Name1	Name1	Name1	Name1	Name1	Name1	Name1	Name1	Name1
Name2	Name2	Name2	Name2	Name2	Name2	Name2	Name2	Name2
Name3	Name3	Name3	Name3	Name3	Name3	Name3	Name3	Name3
Name4	Name4	Name4	Name4	Name4	Name4	Name4	Name4	Name4

I want all of this combined under one feild like this.

Assignee_1_9

Name1

Name2

Name3

Name4

Name1

Name2

Name3

Name4

Name1

Name2

Name3

Name4

Name1

Name2

Name3

Name4

Name1

Name2

Name3

Name4

Name1

Name2

Name3

Name4

[prat1507]

Hi

You can Load the data as

Load

Assignee1 as Assignee_1_9,

Assignee2 as Assignee_1_9,

Assignee3 as Assignee_1_9,

Assignee4 as Assignee_1_9,

Assignee5 as Assignee_1_9,

Assignee6 as Assignee_1_9,

Assignee7 as Assignee_1_9,

Assignee8 as Assignee_1_9,

Assignee9 as Assignee_1_9 ;

Regards

Pratyush

[marcus_sommer]

You could transform your data with The Crosstable Load.

- Marcus

[Anonymous]

tried this, but when I reload the data it fails and asks to revert old data.

I am currently trying the following...

Load

Assignee1 &'/'& Assignee2 &'/'& Assignee3 &'/'&Assignee2 &'/'& .......... as Assignee_1_9 and its working, but it is not loading the sames names again.

[prat1507]

Hi Don

You need to write it as

Load

Assignee1 as Assignee_1_9,

Assignee2 as Assignee_1_9,

Assignee3 as Assignee_1_9,

Assignee4 as Assignee_1_9,

Assignee5 as Assignee_1_9,

Assignee6 as Assignee_1_9,

Assignee7 as Assignee_1_9,

Assignee8 as Assignee_1_9,

Assignee9 as Assignee_1_9

from 'Your data source'.

Can you please share a sample app?

Regards
Pratyush

[jwbadger3]

I don't think this will work as Qlik wants a unique field name. You might be able to do something fancy with Cross Tables but your easiest bet is to load each field renamed and then concatenate:

Load

     Assignee 1 as Assignee_1_9

From SOURCE TABLE;

Concatenate

Load

     Assignee 2 as Assignee_1_9

From SOURCE TABLE;

etc...

To do this even easier you can create a little loop...

For i = 1 to 9

  Let vField='Assignee'&$(i);

    Assignee_1_9:

    Load

         $(vField) as Assignee_1_9

    [WHATEVER YOUR TABLE LOCATION IS];

next;

[MarcoWedel]

Hi,

one example using the already proposed Crosstable Load:

CrossTable (Assignee, Assignee_1_9)
LOAD RecNo() as ID, *
FROM [https://community.qlik.com/thread/264351] (html, codepage is 1252, embedded labels, table is @1);

IMO it's quite a convenient way of performing the transformation you asked for.

hope this helps

regards

Marco