Cross Table / Generic

[vengadeshpalani]

Hi All,

I have table like INPUT what is the best way to change like OUTPUT

INPUT

ID	Type	Value
1	A	2.5
1	B	3
2	A	2.6
2	B	4.5
3	A	5
3	B	5.5
4	A	4
4	B	4.1

OUTPUT

ID	A	B
1	2.5	3
2	2.6	4.5
3	5	5.5
4	4	4.1

[Frank_Hartmann]

I think you already gave the answer. Use Generic Load

Generic
LOAD ID, Type, Value
FROM [https://community.qlik.com/thread/305091]
(html, codepage is 1252, embedded labels, table is @1);

OUTPUT:

[arvind1494]

Use generic load you will get expected output.

[vishsaggi]

Why don't you just use  a pivot table chart. Like

Add pivot table

Dim: ID, Type

Expr: = Sum(Value)

Then drag your Type column to top of expr like below:

once you drag your Type field you can see like below

[sunny_talwar]

I agree, why not use Pivot table?

[vengadeshpalani]

I need this transformation in script level so cant use pivot option,when i use generic option it create separate table.how can i concatenate automatically?  

[sunny_talwar · Accepted solution]

Try this

Table:

LOAD * INLINE [

    ID, Type, Value

    1, A, 2.5

    1, B, 3

    2, A, 2.6

    2, B, 4.5

    3, A, 5

    3, B, 5.5

    4, A, 4

    4, B, 4.1

];

FinalTable:

LOAD Distinct ID

Resident Table;

FOR i = 1 to FieldValueCount('Type')

LET vType = FieldValue('Type', $(i));

Left Join (FinalTable)

LOAD Distinct ID,

Value as [$(vType)]

Resident Table

Where Type = '$(vType)';

NEXT

DROP Table Table;