Transaction Counting for Data Cleansing

[andrewplaice]

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

[Maskell_Rascal · Accepted solution]

Hey @andrewplaice 

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.

If this solves your issue please mark the answer as correct, if not let me know!

Thanks!

Phil

[RobertOdera · Accepted solution]

Hi, @andrewplaice 

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

Solution - zeros converted to Null()

Solution - zeros converted to Blank

Workflow

Please see the file attached.

Cheers!

[andrewplaice]

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

[andrewplaice]

Spoiler

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

[RobertOdera]

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!

[RobertOdera]

Oh oh, @andrewplaice 

Your Spoiler is empty.

Kindly re-post so others can leverage as well.

Cheers!