Qlik Community

Ask a Question

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor
Contributor

Merging column t

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 TestQ2.2 TestQ2.3 Test
abcdefabc
defabcabc

def

abcdef

Result needed:

Q2
abc
def
def
def
abc
abc
abc
abc
def

Any idea?

Many Thanks,

Hasvine

1 Solution

Accepted Solutions
Champion III
Champion III

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

View solution in original post

5 Replies
Partner
Partner

Hi,

Try with concatenate with three loads.

Saludos.

Champion III
Champion III

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

View solution in original post

Contributor
Contributor

Thank you.

It works great.

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

         

RegionQ2.1 TestQ2.2 TestQ2.3 Test
Northabcdefabc
Southdefabcabc
East

def

abcdef

Result needed:

RegionQ2
Northabc
Southabc
Southabc
Northabc
Eastabc
Northdef
Southdef
Eastdef
Eastdef

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

Many thanks for your help.

Kind Regards,

Hasvine

Champion III
Champion III

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.

Capture.PNG

Contributor
Contributor

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);Merging Columns.PNG