Conditional Labeling Question/Help

[ghtill]

Hello I am trying to label a column based on certain criteria. Basically I have charges and refunds and want to label the refunds and the associated charges with a column descr with 0 and charges without refunds a 1. I have tried using the multi-row formula tool but have not been able to figure out how to associate one refund with only one charge if this makes sense.

I currently have these if statement for the multi-row formula tool (which are repeated out to the +7 rows):

If [entryid]=[row-1:entryid] and ([amount]*-1)=([row-1:amount]) then 0

If [entryid]=[row+1:entryid] and ([amount]*-1)=([row+1:amount]) and [type] = 'refund' then 0

This will obviously counts multiple charges that are not associated with one refund and would not cover all prior rows:

It should also be noted this is sorted by date time in descending order (grouped by id)

Current example:

ID	amount	type
2	-250	refund
2	-250	refund
2	-50	refund
2	150	charge
2	50	charge
2	250	charge
2	50	charge
2	250	charge
2	250	charge

Expected:

ID	amount	type	descr
2	-250	refund	0
2	-250	refund	0
2	-50	refund	0
2	150	charge	1
2	50	charge	0
2	250	charge	0
2	50	charge	1
2	250	charge	0
2	250	charge	1

Any help or direction would be much appreciated!

[IraWatt]

Hey @ghtill,

Definetly a round about way but here is one way of doing this:

I essentially find out how much change is still left for each ammount then use a multirow formula to count down the number of charges of that ammount. If it is less then or equal to the number of charges left to fill I put a 1.

Any questions or issues please ask

Ira Watt
Technical Consultant
Watt@Bulien.com 

[ghtill]

@IraWatt Thank you for help I think I understand the processes behind your workflow. However when applied to another test case I find this solution does not work. In the attached excel file I manual entered the descr column to what it should be. Thank you again for all your help. 

[IraWatt · Accepted solution]

AHhhh @ghtill I think I understand you data now. I think you made an error in the example doc though:

My new approach uses an iterative macro as your data is sequential as you say.

If you want to learn more about Macros the community has some really quick interactive videos on getting to grips with them here https://community.alteryx.com/t5/Interactive-Lessons/tkb-p/interactive-lessons/label-name/Macros

[ghtill]

apologies for the delayed response. Thank you again for all your help and your solution worked for all my test cases. Will be going over the macro materials you provided to gain a better grasp of them.