Report with subtotal fields - Copy subtotal to each relevant row

[Becky_Chambers]

Hello - I need to add a column to the below report which would assign the subtotal field name (* ...) to each row of data it relates to.  Which tool(s) should I use?  Note there are other subtotals in the report with more than one row of data.  Thank you!!

[IraWatt]

Hey @Becky_Chambers,

Without knowing the full logic you want to apply its hard to say. The most common way to create a new column would be with a formula tool like this (attached example below):

However if you need to Parse with more complex logic the Regex tool maybe what you need

HTH,

Ira

[Becky_Chambers]

Thank you.  I am summarizing the data by GL account and cost center.  I would have previously formatted manually in excel by copying the cost center field into a new column like this:

[Becky_Chambers]

@IraWatt I added some more information below.  Thank you!

[IraWatt · Accepted solution]

I'm not entirely sure on your logic, can we identify what goes in the new column by the "*"? It looks like what you need though is a multi row formula tool (circled below) like this (attached workflow):

The multi row formula works top down so I sorted the records backwords to capture the logic from your screen shot. If you have any questions on how this workflow works make sure to ask :) 

[Becky_Chambers]

Thank you @IraWatt!  The multi row formula is exactly what I needed.  I am not sure how the formula is telling Alteryx to stop at the next *, but it works!  At the end of my flow I can summarize by cost center and GL which was the goal.  

[IraWatt · Accepted solution]

Eeyyyy glad it worked @Becky_Chambers ! 

IF Left([Row-1:Cost Centers],1) ="*" THEN [Row-1:Cost Centers]
ELSEIF Left([Cost Centers],1) ="*" THEN NULL() 
ELSE [Row-1:New column] ENDIF

Maybe check out one of Alteryx's videos on multi row formula but essentially the tool goes row by row and applies logic for the new column. The top line says if the left most character in the cost centre above is "*" then take the value of that cost centre. If that is not true then second line checks if the left most character cost centre on the same row is "*" then put NULL. The final line just says take the value of the row above in the new column.

HTH (probably check the video if not :) )

Ira