topic Re: Merging column t in QlikView

Merging column t

hasvine25 — Wed, 16 May 2018 10:28:06 GMT

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

Re: Merging column t

fvelascog72 — Wed, 16 May 2018 15:30:04 GMT

Hi,

Try with concatenate with three loads.

Saludos.

Re: Merging column t

vishsaggi — Wed, 16 May 2018 15:45:52 GMT

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);

Re: Merging column t

hasvine25 — Thu, 17 May 2018 07:26:09 GMT

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

Re: Merging column t

vishsaggi — Thu, 17 May 2018 17:31:30 GMT

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.

Re: Merging column t

sreekanth_mstr — Thu, 17 May 2018 18:45:34 GMT

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);