Forum Discussion
Transform data: rows to columns based on values in rows
- 8 years ago
I've been searching the community/forum for a solution and came up with one. It's quite lengthy, so I'll stick to the highlights.
I am posting it for others to enjoy, but also to check if somebody else may have an idea on how to do this more easily so.
Step 1 - Load the source data
Step 2 - Create 3 new tables using 'Reference' (so I only have to add certain columns once, see step 5)
Step 3 - Filter the newly created tables on action code, i.e. the table 'SourceData_ArrivalTime' has a filter on column 'Action code description'='Arrival Time'.
Step 4 - Create a 4th new table (also using 'Reference') to act as a Key or Main table in which all 'resulting' times will be stored
Step 5 - I needed a unique key to combine the Key table and the Time tables. The unique combination I chose is 'Notification ID - Date - Entry Created By'. Here comes the beauty of referencing tables: I created a new column in the SourceData table named ConcatKey and it showed up in all tables! :-)
Step 6 - Use the Merge Queries function in the query editor to add the different Time tables to the Key table, link using the ConcatKey.
Step 7 - Expand the merged queries, and make it show only the applicable time (e.g. SourceData_ArrivalTime)
Step 8 - Add a column to calculate the time difference
Step 9 - Be smug and happy that I found above solution :-)Please find below some screenshots which may help you reproduce and/or clarify what I tried to explain in above steps.
Again, if anybody else knows how to do this more quickly/smartly, please let me know!
I've been searching the community/forum for a solution and came up with one. It's quite lengthy, so I'll stick to the highlights.
I am posting it for others to enjoy, but also to check if somebody else may have an idea on how to do this more easily so.
Step 1 - Load the source data
Step 2 - Create 3 new tables using 'Reference' (so I only have to add certain columns once, see step 5)
Step 3 - Filter the newly created tables on action code, i.e. the table 'SourceData_ArrivalTime' has a filter on column 'Action code description'='Arrival Time'.
Step 4 - Create a 4th new table (also using 'Reference') to act as a Key or Main table in which all 'resulting' times will be stored
Step 5 - I needed a unique key to combine the Key table and the Time tables. The unique combination I chose is 'Notification ID - Date - Entry Created By'. Here comes the beauty of referencing tables: I created a new column in the SourceData table named ConcatKey and it showed up in all tables! :-)
Step 6 - Use the Merge Queries function in the query editor to add the different Time tables to the Key table, link using the ConcatKey.
Step 7 - Expand the merged queries, and make it show only the applicable time (e.g. SourceData_ArrivalTime)
Step 8 - Add a column to calculate the time difference
Step 9 - Be smug and happy that I found above solution :-)
Please find below some screenshots which may help you reproduce and/or clarify what I tried to explain in above steps.
Again, if anybody else knows how to do this more quickly/smartly, please let me know!