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]

Hi @Shelley5683 

See if this works for you:

IF ([Row-1:Units] = -1 AND [Units] = 1) AND ([Code]< [Row-1:Code]) THEN "Up Code"
ELSEIF ([Row-1:Units] = -1 AND [Units] = 1) AND ([Code] > [Row-1:Code]) THEN "Down Code"
ELSEIF IsNull([Row-1:Code]) AND IsNull([Row+1:Code]) THEN "No Change"
ELSE "" ENDIF

Cheers,

[Shelley5683]

Thank you for the help @Thableaus the Down Code is working, however the Up Code and No Change doesn't. The information is being sorted by visit date, ticket and then code. I also have another scenario to add - if the ticket shows  1 "units" and -1 "units" and no other code is added, then it needs to reflect "invalid code".

Below is the results I received using the formula you provided.

Appreciate your help!

Shelley

[Thableaus]

Hi @Shelley5683 

Take a look at full Multi-Row configuration with attention.

You need to Group By "Ticket Number" and set "Values for Rows that don't exist" option to NULL. Be sure you're doing that, otherwise it won't work as expected, ok?

To add Invalid Code result:

EDIT:

IF ([Row-1:Units] = -1 AND [Units] = 1) AND ([Code]< [Row-1:Code]) THEN "Up Code"
ELSEIF ([Row-1:Units] = -1 AND [Units] = 1) AND ([Code] > [Row-1:Code]) THEN "Down Code"

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

[Shelley5683]

@Thableaus 

I do have these grouped by ticket but I did not have this changed ("Values for Rows that don't exist" option to NULL) but have corrected that. The No Change, Down Code and Invalid Code work, but still not receiving Up Code messages. For the Invalid Code, I am receiving this message within a ticket that is also tagged Down Code. I only need this to reflect the Invalid Code if the ticket only has 2 lines (a line that is negative and a line that is positive), not sure if I can use the multi-row formula tool for this. I did notice that on the ones that should be tagged as Up Code, has 3 lines and the first 2 lines have positive units and the last has a negative. Can I compare the last 2 lines for the -1 and 1 versus the first 2 lines?

[Thableaus · Accepted solution]

@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,

[Shelley5683]

@Thableaus with just a couple modifications this solution works for what I needed to capture. I greatly appreciate all of your assistance! Have a great day!

Thank you,

Shelley