multi-row formula?

Shelley5683

I am need to add the column "Code Change" after determining what tickets were changed or not changed. I need the "Code Change" column only to reflect the comment on the line that was changed/not changed. To determine if the ticket code has changed, I am using the "Units" column to identify if there is a positive and negative for the same ticket and code. If there is then the ticket has a code change and the "new line" will need to reflect the comment. If the "Units" column doesn't have a positive and negative for the same ticket and code then it will need to reflect "no change". Suggestions for best solution?

Visit Date	Ticket Number	Code	Units	Provider	Code Change
4/12/2019	439315	99214	1	Jane Doe
4/12/2019	439315	99214	-1	Jane Doe
4/12/2019	439315	99213	1	Jane Doe	Up Code
4/12/2019	439326	99213	1	John Smith
4/12/2019	439326	99213	-1	John Smith
4/12/2019	439326	99214	1	John Smith	Down Code
4/12/2019	439324	99203	1	Jill Hill	No Change
4/12/2019	439339	99202	1	Sam Silly	No Change

Thableaus

@Shelley5683 

Ok, as far as I want to help you out, I kindly ask that you provide me all the possibilities and rules for this to work.

Otherwise, we'll never finish this topic.

I changed the flow and divided it into 2 steps. I thought it would be better to work this way.

- Added a Record ID

- Added a Summarized Tool to see which records have code variation

- Sorted by Record ID 

- Used Multi-Row Formula Tool to check if it's Up Code or Down Code with formula:

IF [Max_RecordID] > [Row-1:Max_RecordID] AND [Code] < [Row-1:Code] THEN
"Up Code"
ELSEIF [Max_RecordID] > [Row-1:Max_RecordID] AND [Code] > [Row-1:Code] THEN
"Down Code"
ELSE "" ENDIF

- Filtered only Up and Down code

- Joined that to original dataset to bring Code Change (used a Left Join)

- Sorted again by record ID to put things in order

- Multi-Row Formula Tool to update the Code Change Field.

Used the follow formula:

IF IsNull([Row-1:Units]) AND IsNull([Row+1:Units]) THEN "No Change"
ELSEIF IsNull([Row-1:Units]) AND IsNull([Row+2:Units]) AND [Code] = [Row+1:Code] THEN "Invalid Code"

ELSE [Right_Code Change] ENDIF

* The Invalid code I supposed that IF row before is null (no value), two rows after is null too (no value) and Code = Row +1 Code then this is an Invalid Code.

The full workflow is attached on version 2018.4

Cheers,

WorkflowCode1.yxmd