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
Solved! Go to 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
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!
This multi-row formula could actually be used to solve my problem without the subsequent formulas. Nice work - thank you.
This is a creative solution that works as well. Thanks for responding so quick!
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!