A blank row after invoice name

[Kallis]

Hello Friends

I need to insert a blank row after every invoice name and also, I have to insert the character "#" at the end of that blank row. I tried a few methods but it is not working. Kindly help me.

Regards

Bobji

Invoice Name	Name 1	City	Country	Some Date
AC3	Area 1	Bangalore	India	20240712
MANAGEMENT	Area 2	Hyderabad	India	20240712
MANAGEMENT	Area 2	Hyderabad	India	20240712
MANAGEMENT	Area 2	Hyderabad	India	20240712
MANAGEMENT	Area 2	Hyderabad	India	20240712
MANAGEMENT	Area 2	Hyderabad	India	20240712
MANAGEMENT	Area 2	Hyderabad	India	20240712
MANAGEMENT	Area 2	Hyderabad	India	20240712
MANAGEMENT	Area 2	Hyderabad	India	20240712
MANAGEMENT	Area 2	Hyderabad	India	20240712
MANAGEMENT	Area 2	Hyderabad	India	20240712
MANAGEMENT	Area 2	Hyderabad	India	20240712
HOSTED	Area 3	Delhi	India	20240712
HOSTED	Area 3	Delhi	India	20240712
HOSTED	Area 3	Delhi	India	20240712
HOSTED	Area 3	Delhi	India	20240712
HOSTED	Area 3	Delhi	India	20240712
HOSTED	Area 3	Delhi	India	20240712
HOSTED	Area 3	Delhi	India	20240712
DIGITAL TRANS	Area 4	Lucknow	India	20240712
DIGITAL TRANS	Area 4	Lucknow	India	20240712
DIGITAL TRANS	Area 4	Lucknow	India	20240712
DIGITAL TRANS	Area 4	Lucknow	India	20240712
DIGITAL TRANS	Area 4	Lucknow	India	20240712
SOCIAL	Area 5	New York	America	20240712
SOCIAL	Area 5	New York	America	20240712
SOCIAL	Area 5	New York	America	20240712
SOCIAL	Area 5	New York	America	20240712
SOCIAL	Area 5	New York	America	20240712

[caltang · Accepted solution]

Like so?

[caltang]

Basically what I have done is to generate a row ID that is unique to how the data exists from your structure. This is important as your request involves an ordinal structure. 

Then, because of your condition of having an empty row upon each unique invoice name, I used a Tile tool to generate a unique number set that corresponds to your Invoice Name. So any new invoice names will have a new set ID, and a new running sequence ID. 

From there, I used the sort tool just to visualize what I mean better. You will see your data having an order, and a structured count for each unique Invoice Name and a sequence order.

Then, a Block Until Done is used just to make it look better - splitting the first segment to get the unique list of Set IDs from the tile tool, then getting the max sequence of each set. From there, I join it back with the data to see which data set corresponds to the max.

The formula tool thereafter just adds 1 to both the tile and also the Record ID, and I nullify all fields via the Multi Field formula tool to get what you desire, then add one more formula tool to add an extra column with # as a value. Since you did not name it properly, I left it as is.

Then, I just use the 2nd output from the original Block Until Done, and union it with the data I got from the join and formulas above. Then sort it, and voila. You get an empty row after each new invoice name.

If you want to experiment further, you will need to play with it more and detail it better.

[Kallis]

Thanks @caltang 

This is exactly what I want. Let me try your solution.

Also, thanks for the detailed description. It will be helpful for someone like me who does not have experience in Alteryx.

[CoG · Accepted solution]

Here is an another way to solve the problem that simplifies the approach. You could incorporate the Tile Tool, if the sort order is important, otherwise, this should do what you need.

1. Summarize Tool gives us one record for every [Invoice Name] (which will later become our null row)
2. Formula Tool to add "#"
3. Union to stack both sets of data, Alteryx will add missing columns to both sets of data, but set to null (which is exactly what we need, but free of charge)
4. Sort so that null records come last for every [Invoice Name]
5. Nullify the [Invoice Name] for only the extra row with handy IIF() statement.

Hope this helps and Happy Solving!

[caltang]

Very clean!

[Kallis]

Thanks @caltang @CoG 

I will mark this as Solution so that others can benefit. 

[Kallis]

Hello @caltang 

A small change is required. The "#" instead of at the end of the new row, must be at the end of every invoice. 

Can you please help.

Thanks

Bobji

[caltang]

You can try it first! Use a formula tool to update the extra column.

If CONTAINS(Fields,"#")

THEN NULL()

ELSE "#"

ENDIF.

Try.