Concatenate table fields

[jeckstein]

Hi,

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

ID	Andy	Rob	Amanda
AB1	300	200
AB2	400
AB3	100	300	500
AB4	200	100
AB5	500
AB6			600
AB7	700		300

I need the result to look like this:

ID	Type	Number
AB1	Andy	300
AB1	Rob	200
AB2	Andy	400
AB3	Andy	100
AB3	Rob	300
AB3	Amanda	500
AB4	Andy	200
AB4	Rob	100
AB5	Andy	500
AB6	Amanda	600
AB7	Andy	700
AB7	Amanda	300

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

[Anonymous]

Use cross Load, check this?

The Crosstable Load

[MindaugasBacius]

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:

[sunny_talwar]

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;