Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
jeckstein
Not applicable

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
balrajahlawat
Not applicable

Re: Concatenate table fields

Use cross Load, check this?

The Crosstable Load

MindaugasBacius
Not applicable

Re: Concatenate table fields

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

sunny_talwar
Not applicable

Re: Concatenate table fields

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