topic Re: How to find multiple subheaders and convert each to columns for rows below in Alteryx Designer Desktop Discussions

How to find multiple subheaders and convert each to columns for rows below

rdphillips — Tue, 13 Feb 2024 14:33:46 GMT

I've attached an excel file with 2 sheets.

The Sample sheet contains an example of scrubbed data (trimmed heavily), in rows 2, 6, 10, 14 are all subheaders. The subheader only applies to the rows below the subheader and above the following subheader. For example subheader in row 2, only applies to rows 3-5. I'm trying to find a way to move these subheaders into a column for the applicable rows.

The Target sheet, shows what I am I'm looking for as an end result.

I'm relatively new to Alteryx, so this type of data manipulation I haven't been able to figure out. I've only been successful moving Subheader1 to a column and applying it to the entire dataset - so not quite what is needed. Appreciate any assistance.

Re: How to find multiple subheaders and convert each to columns for rows below

aatalai — Tue, 13 Feb 2024 14:52:19 GMT

Is there an easy way to identify the rows that relate to them? If so use a formula tool to tag them and then use the cross tab tool with the new tag column being the names you get the column from

Re: How to find multiple subheaders and convert each to columns for rows below

Raj — Tue, 13 Feb 2024 14:56:12 GMT

@rdphillips Find the solution attachment.

Re: How to find multiple subheaders and convert each to columns for rows below

DataNath — Tue, 13 Feb 2024 14:57:31 GMT

Hey @rdphillips we can achieve this with a couple of tools. We do this by:

1) Using a Multi-Row Formula to pull out rows containing header values and filling them down

2) Filtering out rows with null values where we'd expect actual results

3) Rearranging to get your desired format

Hope this helps!

Re: How to find multiple subheaders and convert each to columns for rows below

rdphillips — Tue, 13 Feb 2024 14:57:41 GMT

Rows that correspond to the subheaders are those rows between them. So in the example case attached subheader1 applies to rows 3-5, subheader2 applies to rows 7-9, subheader3 applies to rows 11-13, and subheader 4 applies to rows 15-17. The caveat to the real data, subheader1 will always be on row 2, but the other subheaders could be anywhere in the data depending on the activity for these groups.

In other words in the full dataset, in month one subheader1 could apply to row 3-17 and subheader2 to rows 19-123, and in month two subheader1 could apply to rows 3-82 and subheader 2 to rows 84-99.

Re: How to find multiple subheaders and convert each to columns for rows below

JS1989 — Tue, 13 Feb 2024 15:01:14 GMT

Hope this helps:)

Re: How to find multiple subheaders and convert each to columns for rows below

rdphillips — Tue, 13 Feb 2024 15:05:12 GMT

Thank you @raj, @DataNash, and @JS1989 with these slightly different solutions I can see how the different pieces are utilized - very valuable. Appreciate the quick response.

Re: How to find multiple subheaders and convert each to columns for rows below

AmitMiller — Tue, 13 Feb 2024 15:07:09 GMT

Hi @rdphillips , here's a possible way of doing this:

Regarding the way of thinking - the first part would be how to move the group values to a new column. Usually, the Formula tool gets the job done via an IF condition

Once you get that - you need to be familiar with window functions which are used via the multi-row formula tool. See documentation here

This tool can be used for a lot of scenarios, and it's worth looking into its one-tool example as well!

Hope this helps =] see workflow attached