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

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

Counting for Data Cleansing.yxmd