Transform Table II

[#1]

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.

[jagan]

HI,

Check the attached file.

Use ID, name, type as Dimensions.

and Sum(value) AS expression

Now drag type dimension horizontally.

Regards,

Jagan.

[swuehl]

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;

[#4]

Sorry Jagan, I need do it in script.

Thanks a lot.

[marcel_olmo]

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.