Convert rows to columns in Data Manager

[asmaldon]

Hello!

I'm trying to set up a data model that is connected to an Oracle database as the data source. I need to pivot columns in one of the tables converting rows to columns. I see the option to unpivot, but I need to do the opposite. I know this is an option in Power BI, but I can't figure out how to do it in Qlik Sense. Can someone help me with this issue?

Thanks.

[Lisa_P]

Take a look at this article on the generic load. It does involve scripting, but it should get your result.

https://community.qlik.com/t5/Qlik-Design-Blog/The-Generic-Load/ba-p/1473470 

[asmaldon]

Thank you! Are you aware of a way to do it without editing the script. I prefer to do as much as I can through the Data Manager because I believe editing the script disables the Data Manager.

[Lisa_P]

You can use both. Load the external data using Data Manager and then transform it in another tab in the Data Load Editor using a Resident load, if you have some sample data I can assist with the code.

[asmaldon]

The attached file is similar to the table I'm trying to transform. I need to pivot the table so that the different roles become columns or attributes and the full names are the values. Names have been changed to protect the innocent and actual table is much larger, but this one should inform the script.

Thanks.

Tony

[Lisa_P]

This is my code, not sure if its the result that you want ..

If you want to load the original using Data Manager, you can then just create a section after that in the Data Load Editor by clicking the + Symbol, call it transform, or whatever you like and add the code. Please adjust to match your dataset.

Data:    // You may have already loaded this in Data Manager
Load * FROM [lib://Downloads/Qlik Rows to Columns.xlsx]
(ooxml, embedded labels, table is Merge2);

Table:
Generic LOAD
DRAFT_BILL_NO,
REF_ROLE.ROLE_DESC,
BTS.PERSON.PERSON_FULL_NAME
Resident Data;

Result:
Load Distinct DRAFT_BILL_NO Resident Data;

For i=0 to NoOfTables() -1
TableList:
Load TableName($(i)) as TableName AutoGenerate 1
Where WildMatch(TableName($(i)), 'Table.*');
Next i;

For i= 1 to FieldValueCount('TableName')
Let vTable = FieldValue('TableName', $(i));
Left Join (Result) load * Resident [$(vTable)];
Drop Table [$(vTable)];
Next i;

Drop Table TableList;

[Lisa_P]