Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jsingh71
Partner - Specialist
Partner - Specialist

How I merge Multiple columns into one column??

I have following Table :

MonoColorSipmlexDuplex
1234
5678
9101112

Now I want to merge All 4 column into one column name ' Type ' and add one additional column name ' Value ' where I show the sum like following:

Type               Value

-------             ------------

Mono            15

Color             18

Simplex         21

Duplex          24

How I got this??

1 Solution

Accepted Solutions
kruppas78
Contributor III
Contributor III

Hi,

so you can get this table:

Basis:

LOAD * INLINE [

    Mono, Color, Sipmlex, Duplex

    1, 2, 3, 4

    5, 6, 7, 8

    9, 10, 11, 12

];

Table:

Load

'Mono' as Type,

SUM(Mono) as Value

RESIDENT Basis

GROUP BY 'Mono';

LOAD

'Color' as Type,

SUM(Color) as Value

RESIDENT Basis

GROUP BY 'Color';

LOAD

'Sipmlex' as Type,

SUM(Sipmlex) as Value

RESIDENT Basis

GROUP BY 'Sipmlex';

LOAD

'Duplex' as Type,

SUM(Duplex) as Value

RESIDENT Basis

GROUP BY 'Duplex';

View solution in original post

6 Replies
Anonymous
Not applicable

you can try something like this..

Table:

Load

'Mono' as Type,

Mono as Value

From $(FileName);

Concatenate (Table)

LOAD

'Color' as Type,

Color as Value

From $(FileName);

Concatenate (Table)

LOAD

'Sipmlex' as Type,

Sipmlex as Value

From $(FileName);

Concatenate (Table)

LOAD

'Duplex' as Type,

Duplex as Value

From $(FileName);

hope that helps..

kruppas78
Contributor III
Contributor III

Hi,

so you can get this table:

Basis:

LOAD * INLINE [

    Mono, Color, Sipmlex, Duplex

    1, 2, 3, 4

    5, 6, 7, 8

    9, 10, 11, 12

];

Table:

Load

'Mono' as Type,

SUM(Mono) as Value

RESIDENT Basis

GROUP BY 'Mono';

LOAD

'Color' as Type,

SUM(Color) as Value

RESIDENT Basis

GROUP BY 'Color';

LOAD

'Sipmlex' as Type,

SUM(Sipmlex) as Value

RESIDENT Basis

GROUP BY 'Sipmlex';

LOAD

'Duplex' as Type,

SUM(Duplex) as Value

RESIDENT Basis

GROUP BY 'Duplex';

Not applicable

Hi Jai,

The above is one way to do it, however if you have many values in 'Type' or if they vary, you code may need a lot of manual intervention.

I would rather use Crosstable to solve this. Please find an example file attached. Hope it works for you

Regards

RL

jsingh71
Partner - Specialist
Partner - Specialist
Author

Thanx a lot lakhina.

jsingh71
Partner - Specialist
Partner - Specialist
Author

Thank you very much Aadil.

jsingh71
Partner - Specialist
Partner - Specialist
Author

Thanx Stefan its working.