Varying Rows Converted to Columns

mskusace

New Member
Joined
Jan 2, 2019
Messages
18
Good Morning,

I have a report I pull that has a basic data structure of the table below. I have masked and simplified the data.

IDSub_IDUserRole
1APerson1Supervisor
1APerson2Analyst
1BPerson3Assistant
2CPerson4Supervisor
2CPerson5Analyst
2CPerson6Analyst
2CPerson7Analyst
2CPerson8Intern

<tbody>
</tbody>

I combine the "ID" and "Sub_ID" to create a composite key for the current processing.
Notice that there are multiple entries for ID+Sub_ID because there are multiple roles for that combination of ID+Sub_ID.
What I do for the data processing is to make the ID+Sub_ID a unique column. Then I was planning on turning the roles into different columns. The problem that I run into is that it is not consistent.
For example, 1A (ID+Sub_ID) has 2 roles, 1B has 1 role, 2C has 5 roles, and 3 of them are the same, but they still need to be in separate columns. I need to be able to view each role for each ID+Sub_ID after the data is processed.

Then I thought about feeding this processed data into Qlik Sense, a visualization tool, and make the data more of a database structure with all 4 of those columns as dimension tables and then one fact table using all those foreign keys from the dimension tables. Ultimately, a user could then just click into their combination of ID+Sub_ID and see the varying amounts of "Roles". I am not sure what the best method was and I guess this is more of a data management question and how should I proceed?

I apologize if this is in the wrong section.

Please let me know if you need any more clarification.
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

mskusace

New Member
Joined
Jan 2, 2019
Messages
18
I apologize for bumping my thread, but I could not figure out how to edit my original post. I am just posting some clarification. The potential output I was thinking would be the following then:

ID + Sub_IDSupervisorAnalystAnalystAnalystAssistantIntern
1APerson1Person2
1BPerson3
2CPerson4Person5Person6Person7Person8

<tbody>
</tbody>

However, my question still stands on the best way to manage and process the data.
 

Watch MrExcel Video

Forum statistics

Threads
1,089,908
Messages
5,411,164
Members
403,346
Latest member
SuperHeiks

This Week's Hot Topics

Top