Transposing, Filtering and Summarizing without Transpose Tool

[ztriano]

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)

[mceleavey]

Hi @ztriano ,

can you provide some mock data representative of what you have so we can see what you're trying to do?

M.

[ztriano]

@mceleavey See attached.

[Felipe_Ribeir0]

Hi @ztriano 

One way of doing this without transposing:

[ztriano]

Hi @Felipe_Ribeir0 the problem is that we need it to be dynamic, as we don't know what the column headers will be or how many columns will need to be concatenated, we just need the routine to be able to detect it and combine as many as we have at that time.

[mceleavey]

@ztriano , can you confirm the ACTUAL format of the data.

The data you have provided has "Before Transformation" and "After transformation" at the top. I'm assuming that's not there. So would the input format be in Excel with the "Staff, X" etc as row as the A cell row?

Also, I'm not sure if what you are trying to do can be done without the Transpose tool, or indeed if it can be done, if it would be any quicker.

M.

[mceleavey]

@ztriano ,

ok, I've had a look at this and although there is a way of doing this it would require batch macros, and given the columns themselves are dynamic, the best and quickest way of doing this is by using the Transpose tool.

Given your data sizes this would also be a call for the server.

Another thing you can look into is checking whether you can stage the data. For example, is the data that size because you're loading it all in, then tomorrow you only need to load the last 24 hours?

Anyway, I hope this helps,

M.

[Felipe_Ribeir0]

Concatenate or sum multiple columns dynamically could be much more easier/less computing expensive to do i think. Using your topic as reference, i posted a new idea.

https://community.alteryx.com/t5/Alteryx-Designer-Ideas/Enhancement-on-the-Multi-Field-formula/idi-p...

Please vote for it if you understand that this would be useful.