topic Re: generate multiple rows from on row in load script in QlikView

generate multiple rows from on row in load script

dcd123456 — Fri, 12 Dec 2014 06:53:46 GMT

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

Re: generate multiple rows from on row in load script

tresB — Fri, 12 Dec 2014 07:06:07 GMT

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.

Re: generate multiple rows from on row in load script

aveeeeeee7en — Fri, 12 Dec 2014 07:24:41 GMT

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

Re: generate multiple rows from on row in load script

PrashantSangle — Fri, 12 Dec 2014 07:26:19 GMT

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

Re: generate multiple rows from on row in load script

dcd123456 — Mon, 15 Dec 2014 09:29:31 GMT

thanks to all

regards