community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Generate Row Tool to Insert Blank Row After Change in Grouping

Meteor

I am generating a report that tracks monthly metrics for specific customers/contracts and I am trying to insert a blank row in between each customer/contract group.  I am able to insert a blank row after each row on my report, but I want to be able to insert the blank row only after the last row for each customer/contract. 

 

My report data is similar to the below:

 

BUREAUTARIFFCONTRACT NUMBERCUSTOMERYYYYMMREFUND AMOUNTTONSTALLY
AA01AA-01A201807$0.0000
AA01AA-01A201808$0.0000
AA01   $0.0000
AC05AC-05B201804$500.00500500
AC05AC-05B201805$500.00500500
AC05AC-05B201806$500.00500500
AC05AC-05B201807$500.00500500
AC05AC-05B201808$750.00750750
AC05   $2,750.0027502750

 

Note, there can be varying line items per customer/contract.

 

I would like my report data to look like the below:

 

BUREAUTARIFFCONTRACT NUMBERCUSTOMERYYYYMMREFUND AMOUNTTONSTALLY
AA01AA-01A201807$0.0000
AA01AA-01A201808$0.0000
AA01   $0.0000
        
AC05AC-05B201804$500.00500500
AC05AC-05B201805$500.00500500
AC05AC-05B201806$500.00500500
AC05AC-05B201807$500.00500500
AC05AC-05B201808$750.00750750
AC05   $2,750.0027502750
        

 

I am able to insert a blank row after each individual row in my report using Generate Row/Multi-Field Formula; however, am unable to figure out how to adjust these tools to generate blank rows after each change in customer/contract.  Is there a way to do this?  Any help would be greatly appreciated!

Alteryx Partner

Could you create a new stream with a summary tool that does a group by on [BUREAU], union that with the original, sort appropriately and then follow up with a formula tool that blanks out [BUREAU] if IsNull([TALLY])?

 

jwalder's solution is exactly what I was thinking when reading the question.

Meteor

That is a good idea and though it is generating the blank rows that I want, I am unable to sort my data back into same report format once I do this.  For instance, it removes the line of data with my subtotals to the top along with my blank row followed by my monthly totals.  Is there a different way to insert blank rows after each change in bureau/tariff or customer without having to resort?

 

BUREAUTARIFFCONTRACT NUMBERCUSTOMERYYYYMMREFUND AMOUNTTONSTALLY
AA01  [Null]$0.0000
 [Null]  [Null][Null][Null][Null]
AA01AA-01A201807$0.0000
AA01AA-01A201808$0.0000
AC05   $2,750.0027502750
 [Null]  [Null][Null][Null][Null]
AC05AC-05B201804$500.00500500
AC05AC-05B201805$500.00500500
AC05AC-05B201806$500.00500500
AC05AC-05B201807$500.00500500
AC05AC-05B201808$750.00750750
Quasar

What about this approach? 

 

Insert empty rows by group.png

Meteor

This is great! It does generate a blank row in between each group that I have.  I did, however, fail to mention that I have a grand total line which has now moved to the first row in my dataset (because [BUREAU] and [TARIFF] are null, so they sort to the top).  Any ideas as to how I should account for my grand total line and get that back to the last row of my dataset?  I have attached the updated sample workflow.

Just building on Philip's response. I made some tweaks to meet your requirements.

 

Using two Multi Row Formula tools, you can setup a "Bureau ID" and "Line Item ID" to sort all of the records in the correct order.

 

workflow.png

Meteor

This is perfect!  Thank you all for your help :)

Labels