Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Generate Row Tool to Insert Blank Row After Change in Grouping

rwsarisky
7 - 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!

7 REPLIES 7
jwalder
10 - Fireball

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])?

 

steven-barsalou
8 - Asteroid

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

rwsarisky
7 - 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
Philip
12 - Quasar

What about this approach? 

 

Insert empty rows by group.png

rwsarisky
7 - 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.

steven-barsalou
8 - Asteroid

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

rwsarisky
7 - Meteor

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

Labels