We are trying to transpose data without using the transpose tool, as we are pushing millions of lines of data through this Alteryx routine, so we are trying to find the quickest and most efficient way to do this. Our data is a list of screens that need to be prepared a staff and reviewed by a senior and is provided to us in a large matrix with screens that are Assigned denoted with an ‘A’, and Signed off (as in prepared or reviewed) denoted as an ‘S’, and if someone is not assigned to a screen, it’s just [null]. We’d prefer to just see a column for each level (staff or senior), with a list of everyone assigned to that screen in that level with no nulls. Our biggest problem is figuring out how to pull down the field name as that’s the staff/senior’s name that we want populated as data within the matrix. We were inquiring to see whether it was possible to:
- Separate screen assignment data below by level through the dynamic tool > If [field name] contains ‘staff’ or ‘senior‘ only show data from that level
- if row is not null (or contains ‘A’, ‘S’, or ‘U’), then pull [field name] down into data (‘A’ becomes ‘A, Staff, Name1’)
- Create a new column that concatenates each [Assignment Status, Level, Name] for each level (staff and senior)
Input:
Unique Hierarchy | Staff, Name1 | Staff, Name 2 | Senior, Name1
Screen1 | S | [null] | A
Screen2 | A | A | S
Dynamic tool will only bring over Staff Name1 and Staff Name 2 columns, and maybe we can do formulas at this level?
Output:
Unique Hierarchy | Staff | Senior
Screen 1 | S, Name 1 | A, Name1
Screen 2 | A, Name1; A, Name2 | S, Name1
(please note this excludes null data)
