Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Highlighted
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
balrajahlawat
Esteemed Contributor

Re: Concatenate table fields

Use cross Load, check this?

The Crosstable Load

MindaugasBacius
Valued Contributor III

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

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