Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Transpose Values into Columns

Hi,

I am new to QV and would like to ask if there is a macro function or simple code/s that can handle this. Please do note that distinct value for Type can change every month, hence a dynamic code that can handle this future requirement is much preferrable. But if there is a static codes that requires user revision in case of any changes in the raw date, that would be great if you can share as well.

Thank you very much.

RAW DATA

CategoryTypeDateValue
AppleXJan 6, 20135
AppleXJan 16, 201310
AppleYJan 6, 2013100
AppleYJan 16, 2013200
BearXJan 6, 201310
BearXJan 16, 201315
BearYJan 6, 201350
BearYJan 16, 2013100

DESIRED OUTPUT

CategoryDateXYHeade 5
AppleJan 6, 20135100
AppleJan 16, 201310200
BearJan 6, 20131050
BearJan 16, 201315100
3 Replies
Sokkorn
Master
Master

Hi Joy,

You may try this script:

[DS]:

LOAD * Inline [

Category    ,    Type    ,    Date    ,    Value

Apple    ,    X    ,    06-Jan-13    ,    5

Apple    ,    X    ,    16-Jan-13    ,    10

Apple    ,    Y    ,    06-Jan-13    ,    100

Apple    ,    Y    ,    16-Jan-13    ,    200

Bear    ,    X    ,    06-Jan-13    ,    10

Bear    ,    X    ,    16-Jan-13    ,    15

Bear    ,    Y    ,    06-Jan-13    ,    50

Bear    ,    Y    ,    16-Jan-13    ,    100];

[TMP1]:

GENERIC LOAD Category&'|'& Date AS [tmpCategory],Type,Value RESIDENT [DS];

[RESULT]:

LOAD DISTINCT Category&'|'& Date AS [tmpCategory] RESIDENT [DS];

DROP TABLE [DS];

FOR i = 0 to NoOfTables()

TableList:

LOAD '[' & TableName($(i)) &']' AS Tablename AUTOGENERATE 1

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

NEXT i

FOR i = 1 to FieldValueCount('Tablename')

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

LEFT JOIN ([RESULT]) LOAD * RESIDENT $(vTable);

DROP TABLE $(vTable);

NEXT i

[FinalResult]:

NoConcatenate

LOAD

    SubField([tmpCategory],'|',1)    AS [Category],

    SubField([tmpCategory],'|',2)    AS [Date],

    *

Resident [RESULT];

DROP Field [tmpCategory];

Drop Table TableList,RESULT;

See attached file also.

Regards,

Sokkorn

jagan
Luminary Alumni
Luminary Alumni

Hi,

PFA file for solution.  I did this by using Generic load in script.  We can also do this by using pivot table and dragging the Type column to the top the header.

Hope this helps you.

Regards,

Jagan.

Gysbert_Wassenaar

The way to do this imho is to load the raw data as it is and create a pivot table to show the desired output. Create a pivot table with Category, Date and Type as dimensions (in that order) and sum(Value) as expression. On the Presentation tab of the properties window of the pivot table enable the option Always Fully Expanded. Now you need to move the Type dimension to above the expression. Drag the column label to the right above the expression label. When you see a thick blue double-arrowed line you can let go. The Type dimension should now be displayed horizontally.


talk is cheap, supply exceeds demand