Forum Discussion

jvi_bobdebouwer's avatar
jvi_bobdebouwer
Frequent Visitor
8 years ago
Solved

Transform data: rows to columns based on values in rows

Dear all,   I've tried searching the community for a solution, but was unable to find one, mainly because I find it hard to word my problem. Maybe its best I dive into it with using the following e...
  • jvi_bobdebouwer's avatar
    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!