topic Re: Cross Table / Generic in QlikView

Cross Table / Generic

vengadeshpalani — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Cross Table / Generic

Frank_Hartmann — Sat, 16 Jun 2018 09:04:53 GMT

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:

Re: Cross Table / Generic

arvind1494 — Sat, 16 Jun 2018 14:50:12 GMT

Use generic load you will get expected output.

Re: Cross Table / Generic

vishsaggi — Sat, 16 Jun 2018 15:52:41 GMT

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

Re: Cross Table / Generic

sunny_talwar — Sun, 17 Jun 2018 21:25:37 GMT

I agree, why not use Pivot table?

Re: Cross Table / Generic

vengadeshpalani — Mon, 18 Jun 2018 12:23:03 GMT

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?

Re: Cross Table / Generic

sunny_talwar — Mon, 18 Jun 2018 12:29:52 GMT

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;