Qlik Community

Ask a Question

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Qlik Highlights 2020 Giveaway! Watch, reply and have a chance to win a $200 Amazon Gift Card! Watch Video
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