Merging column t

[hasvine25]

Hello All,

I have an excel sheet, where I need to merge 3 columns in 1 as per the tables below:

Actual Table

Q2.1 Test	Q2.2 Test	Q2.3 Test
abc	def	abc
def	abc	abc
def	abc	def

Result needed:

Q2
abc
def
def
def
abc
abc
abc
abc
def

Any idea?

Many Thanks,

Hasvine

[fvelascog72]

Hi,

Try with concatenate with three loads.

Saludos.

[vishsaggi · Accepted solution]

Try this:

CrossTab:

LOAD RowNo() AS Rows,

     Subfield(Field, '|') AS Q2;

LOAD

     [Q2.1 Test]&'|'&[Q2.2 Test]&'|'&[Q2.3 Test] AS Field

FROM

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

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

[hasvine25]

Thank you.

It works great.

Just another small issue is that I have a column Region as below:

Region	Q2.1 Test	Q2.2 Test	Q2.3 Test
North	abc	def	abc
South	def	abc	abc
East	def	abc	def

Result needed:

Region	Q2
North	abc
South	abc
South	abc
North	abc
East	abc
North	def
South	def
East	def
East	def

I thought it would like the region automatically but unfortunately, it did not.

Many thanks for your help.

Kind Regards,

Hasvine

[vishsaggi]

Try this?

CrossTab:

LOAD RowNo() AS Rows,

    Region,

    Subfield(Field, '|') AS Q2;

LOAD

Region,

[Q2.1 Test]&'|'&[Q2.2 Test]&'|'&[Q2.3 Test] AS Field

INLINE [

Region, Q2.1 Test, Q2.2 Test, Q2.3 Test

North, abc, def, abc

South, def, abc, abc

East,  def,    abc, def

];

use straight table chart here

Dim: Region, Rows

Expr: Q2

Then in chart properties - presentation tab hide the column Rows.

[sreekanth_mstr]

This may also work.

Test:
LOAD
    "1"
FROM [lib://Merge Columns/Merge Columns from different tables.xlsx]
(ooxml, embedded labels, table is Sheet1);

Concatenate
LOAD
    "2" as "1"
FROM [lib://Merge Columns/Merge Columns from different tables.xlsx]
(ooxml, embedded labels, table is Sheet2);

Concatenate
LOAD
    "3" as "1"
FROM [lib://Merge Columns/Merge Columns from different tables.xlsx]
(ooxml, embedded labels, table is Sheet3);