# Alteryx Designer

SOLVED

## Transaction Counting for Data Cleansing

Highlighted
8 - Asteroid

Hi all,

I have data coming down that occasionally has missing values (seg and plan#).  In order to build logic in my workflow that can determine what to replace the value with I need to number the transactions.  The issue is that I need the count to reset every time there is a total line (under type).  This is what my data looks like when it first comes down:

 Seg Fund Fund Name Plan # Type ZZZZ Conv. Transfer In ZZZZ Total ABC AAAA Alpha 12345 Fees ABC AAAA Alpha 12345 Fees ABC AAAA 12345 Total CDEF BBBB Bravo 23456 Conv. Transfer In CDEF BBBB 23456 Total WXYZ DDDD Delta 45678 Fees WXYZ DDDD Delta Fees WXYZ DDDD Delta Fees WXYZ DDDD Delta Fees DDDD Delta Total

This is what I would want it to look like so I may make some logical enhancements.

 Count Seg Fund Fund Name Plan # Type 1 ZZZZ Conv. Transfer In ZZZZ Total 1 ABC AAAA Alpha 12345 Fees 2 ABC AAAA Alpha 12345 Fees ABC AAAA 12345 Total 1 CDEF BBBB Bravo 23456 Conv. Transfer In CDEF BBBB 23456 Total 1 WXYZ DDDD Delta 45678 Fees 2 WXYZ DDDD Delta Fees 3 WXYZ DDDD Delta Fees 4 WXYZ DDDD Delta Fees DDDD Delta Total

Let me know if there is anything I can clarify - thanks for your help (as always)!

Andy

Highlighted
8 - Asteroid

You can use a Record ID tool twice to get the desired results. We first assign a record ID to all records, filter out the total lines, use record ID again but rename the field "Count", combine the streams back together, and finally deselect the original Record ID field.

I've attached an example workflow for you.

Thanks!

Phil

Highlighted
12 - Quasar

Please mark as a solution and Like, if this works for you.

Solution - zeros converted to Null()

Solution - zeros converted to Blank

Workflow

Cheers!

Highlighted
8 - Asteroid

This multi-row formula could actually be used to solve my problem without the subsequent formulas.  Nice work - thank you.

Highlighted
8 - Asteroid
Spoiler

This is a creative solution that works as well.  Thanks for responding so quick!

Highlighted
12 - Quasar

Sure thing, @andrewplaice

I just wanted you to see the optics of Null() versus Blank (hence the extra tools).

I'm glad it worked out for you.

Thanks for accepting as a solution, and for the Like!

Highlighted
12 - Quasar

Oh oh, @andrewplaice