Alteryx Designer

Find answers, ask questions, and share expertise about 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:

 

SegFundFund NamePlan #Type
 ZZZZ  Conv. Transfer In
 ZZZZ  Total
ABCAAAAAlpha12345Fees
ABCAAAAAlpha12345Fees
ABCAAAA 12345Total
CDEFBBBBBravo23456Conv. Transfer In
CDEFBBBB 23456Total
WXYZDDDDDelta45678Fees 
WXYZDDDDDelta Fees
WXYZDDDDDelta Fees
WXYZDDDDDelta Fees
 DDDDDelta Total

 

 

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

CountSegFundFund NamePlan #Type
1 ZZZZ  Conv. Transfer In
  ZZZZ  Total
1ABCAAAAAlpha12345Fees
2ABCAAAAAlpha12345Fees
 ABCAAAA 12345Total
1CDEFBBBBBravo23456Conv. Transfer In
 CDEFBBBB 23456Total
1WXYZDDDDDelta45678Fees 
2WXYZDDDDDelta Fees
3WXYZDDDDDelta Fees
4WXYZDDDDDelta Fees
  DDDDDelta Total

 

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

 

Andy

Highlighted
8 - Asteroid

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.

 

Maskell_Rascal_0-1590156074300.png

 

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

 

Thanks!

Phil

Highlighted
12 - Quasar

Hi, @andrewplaice 

 

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

 

Solution - zeros converted to Null()

RNO2_2-1590157811408.png

 

 

Solution - zeros converted to Blank

RNO2_3-1590157831399.png

 

 

Workflow

RNO2_0-1590158075514.png

 

Please see the file attached.

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 

Your Spoiler is empty.

Kindly re-post so others can leverage as well.

Cheers!

Labels