Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

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: 
Partner
Partner

Concatenate table fields

Hi,

I am trying to create a new table from fields in a existing table. The existing table looks like this.

     

IDAndyRobAmanda
AB1300200
AB2400
AB3100300500
AB4200100
AB5500
AB6600
AB7700300

I need the result to look like this:

IDTypeNumber
AB1Andy300
AB1Rob200
AB2Andy400
AB3Andy100
AB3Rob300
AB3Amanda500
AB4Andy200
AB4Rob100
AB5Andy500
AB6Amanda600
AB7Andy700
AB7Amanda300

Notice a few things need to be satisfied.

  1. A additional column "Type" has been created.
    1. type is equal to the filed name that the "number" has been pulled from
  2. A additional column "Number" has been created.
    1. "Number" is the associated number string with each row and type combination

Thanks in advance!!!!

-Jim

3 Replies
Champion
Champion

Use cross Load, check this?

The Crosstable Load

Specialist III
Specialist III

CrossTable(Type, Number)

LOAD ID,

     Andy,

     Rob,

     Amanda

FROM

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

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

Result:

Screenshot_1.jpg

You can further add a resident load to remove the rows where Number is null

Table:

CrossTable(Type, Number)

LOAD ID,

    Andy,

    Rob,

    Amanda

FROM

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

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

FinalTable:

NoConcatenate

LOAD *

Resident Table

Where Len(Trim(Number)) > 0;

DROP Table Table;


Capture.PNG