topic Re: Transpose Values into Columns in QlikView

Transpose Values into Columns

Wed, 28 Aug 2013 02:44:36 GMT

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

Category	Type	Date	Value
Apple	X	Jan 6, 2013	5
Apple	X	Jan 16, 2013	10
Apple	Y	Jan 6, 2013	100
Apple	Y	Jan 16, 2013	200
Bear	X	Jan 6, 2013	10
Bear	X	Jan 16, 2013	15
Bear	Y	Jan 6, 2013	50
Bear	Y	Jan 16, 2013	100

DESIRED OUTPUT

Category	Date	X	Y
Apple	Jan 6, 2013	5	100
Apple	Jan 16, 2013	10	200
Bear	Jan 6, 2013	10	50
Bear	Jan 16, 2013	15	100

Re: Transpose Values into Columns

Sokkorn — Wed, 28 Aug 2013 03:14:05 GMT

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

Re: Transpose Values into Columns

jagan — Wed, 28 Aug 2013 03:19:43 GMT

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.

Re: Transpose Values into Columns

Gysbert_Wassenaar — Wed, 28 Aug 2013 07:15:29 GMT

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.