topic Transform Table II in QlikView

Transform Table II

Thu, 15 Mar 2012 14:42:13 GMT

Hi, need transform this table

ID	name	type	value
1	A	blue	10
1	A	yellow	50
3	C	red	80
3	C	yellow	60
4	D	yellow	40

In this:

ID	name	yellow	blue	red
1	A	50	10	0
3	C	60	0	80
4	D	40	0	0

Some idea.

Thanks.

Re: Transform Table II

jagan — Thu, 15 Mar 2012 14:51:19 GMT

HI,

Check the attached file.

Use ID, name, type as Dimensions.

and Sum(value) AS expression

Now drag type dimension horizontally.

Regards,

Jagan.

Transform Table II

swuehl — Thu, 15 Mar 2012 14:53:35 GMT

If you want to do the transformation to a crosstable in the script, you could do it like:

INPUT:

LOAD ID,

name,

type,

value

FROM

[http://community.qlik.com/thread/49321?tstart=0]

(html, codepage is 1252, embedded labels, table is @1);

tmp:

Generic LOAD ID, name, type, value Resident INPUT;

Result:

load distinct ID, name resident INPUT;

drop table INPUT;

FOR i = 0 to NoOfTables()

TableList:

LOAD TableName($(i)) as Tablename AUTOGENERATE 1

WHERE WildMatch(TableName($(i)), 'tmp.*');

NEXT i

FOR i = 1 to FieldValueCount('Tablename')

LET vTable = FieldValue('Tablename', $(i));

LEFT JOIN (Result) LOAD * RESIDENT $(vTable);

DROP TABLE $(vTable);

NEXT i

drop table TableList;

Transform Table II

Thu, 15 Mar 2012 14:58:17 GMT

Sorry Jagan, I need do it in script.

Thanks a lot.

Re: Transform Table II

marcel_olmo — Fri, 16 Mar 2012 13:17:17 GMT

Hi Pedro how is it going?

In this case, I think the best way to solve it is using the GENERIC LOAD function. Which does a transpose of each field of the table and it links the common fields automatically.

Is quite useful.

Regards, Marcel.