generate multiple rows from on row in load script

[dcd123456]

Hello

I have a table with several field and i need split the original rows in  severals rows with only one field, in the load script.

for example i have the original table like this:

ID	Field1	Impo	field2	impo
a	2013	100 €	2014	10 €
b	2013	10 €
c	2014	200 €	2015	60 €

and i need transform this table in the load scrip to the next table:

ID	Fieldx	Impo
a	2013	100 €
a	2014	10 €
b	2013	10 €
c	2014	200 €
c	2015	60 €

anybody know how can i do this in the load scrip?

thanks

regards

[tresesco]

Try like:

Final:

Load    

          ID,

          Field1 as Fieldx,

          Impo

From <>;

Load

          ID,

          Field2 as Fieldx,

          impo as Impo

From <> where not isnull(Field2);  // this condition might vary accordinly.

It would give you one table 'Final' as expected.

[aveeeeeee7en]

Hi

Use this Script:

ABC:
LOAD * Inline [
ID, Field1, Impo, field2, impo
a, 2013, 100 €, 2014, 10 €
b, 2013, 10 €, ,
c, 2014, 200 €, 2015, 60 €
];

NoConcatenate
BCD:
LOAD
ID,
Field1,
field2,
Field1&'/'&field2 AS FieldKey,
Impo,
impo,
Impo&'/'&impo AS ImpoKey
Resident ABC;
DROP TABLE ABC;

NoConcatenate
CDE:
LOAD
ID,
Subfield(FieldKey,'/',1) AS FieldKey,
Subfield(ImpoKey,'/',1) AS ImpoKey

Resident BCD WHERE LEN(TRIM(Subfield(FieldKey,'/',1)))<>'0' AND LEN(TRIM(Subfield(ImpoKey,'/',1)))<>'0';

Concatenate(CDE)

LOAD
ID,
Subfield(FieldKey,'/',2) AS FieldKey,
Subfield(ImpoKey,'/',2) AS ImpoKey

Resident BCD WHERE LEN(TRIM(Subfield(FieldKey,'/',2)))<>'0' AND LEN(TRIM(Subfield(ImpoKey,'/',2)))<>'0';

DROP TABLE BCD;

The Final Result:

See the Attachment.

Regards

Av7eN

[PrashantSangle · Accepted solution]

Hi,

As per my understanding this is not possible in one load

You have to split your load and conactenate it.

Try like

test:

LOAD * INLINE [
id, field1, Impo, field2, impo
a, 2013, 100, 2014, 10
b, 2013, 10
c, 2014, 200, 2015, 60
];

final_Test:
load id,
field1,
Impo
Resident test;

Concatenate
LOAD id,
field2 as field1,
impo as Impo
Resident test;

drop Table test;

Find attached application for more clearifiaction.

REgards

[dcd123456]

thanks to all

regards