Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

vengadeshpalani
Contributor

Cross Table / Generic

Hi All,

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

INPUT

IDTypeValue
1A2.5
1B3
2A2.6
2B4.5
3A5
3B5.5
4A4
4B4.1

OUTPUT

IDAB
12.53
22.64.5
355.5
444.1
Tags (2)
1 Solution

Accepted Solutions
MVP
MVP

Re: Cross Table / Generic

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;

6 Replies
franky_h79
Honored Contributor

Re: Cross Table / Generic

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
Contributor III

Re: Cross Table / Generic

Use generic load you will get expected output.

vishsaggi
Esteemed Contributor III

Re: Cross Table / Generic

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:

Capture.PNG

once you drag your Type field you can see like below

Capture.PNG

MVP
MVP

Re: Cross Table / Generic

I agree, why not use Pivot table?

vengadeshpalani
Contributor

Re: Cross Table / Generic

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?  

MVP
MVP

Re: Cross Table / Generic

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;