Designer - Isolating the one rows while others combined by two rows

[DanielCarro]

Hi,

I have a task which I am not very sure how to tackle it and need some guidance

So there are thousands of rows as per the example below, so items such as BANK/PAID are 'paired' and the same per PAID/CREDIT, NOTE THE ORDER OF THE PAIRS, so what I need to do is to isolate the row I have highlighted in black as this needs to investigate.

CUSTOMER	TRANSACTION	DATE
A	BANK	01/01/2022
A	PAID	01/01/2022
A	BANK	02/02/20222
A	PAID	02/02/2022
A	BANK	03/03/2022
B	BANK	01/01/2022
B	PAID	01/01/2022
B	PAID	01/02/2022
B	CREDIT	02/02/2022

Kind Regards,

Dan

[Luke_C]

Hi @DanielCarro 

Here's an example of how to flag this using a multi-row formula. by customer, it will check if 'BANK' records are followed by 'PAID'. You can then filter on the flag field to find your items. Hopefully this gets you on the right track. You can use similar logic for Paid/Credit

[DanielCarro]

Hi Luke, can you combine two if statements in the multi-row formula?

[Luke_C]

Hi @DanielCarro 

Yes, you would use an elseif to continue with your logic:

if [TRANSACTION] = 'BANK' and [Row+1:TRANSACTION] != 'PAID' then 1

ElseIf ..... (logic for Paid/Credit)

else 0 endif

Your example data doesn't have a case to flag the second criteria, so not sure what exactly the logic needs to be. My suggestion would be to give it a shot on your own and post back if you have any issues, that's the best way to learn. 

[DanielCarro]

Hi Luke, 

BANK       0

PAID         0

CREDIT   1

PAID        0

Credit should be 0

Using two if statements

if [PAY_CODE] = 'DAILY_SCHEDULE_HOURS' and [Row+1:PAY_CODE] != 'WORKED_EL' then 1 Elseif [PAY_CODE] = 'ANNUAL_LEAVE' and [Row+1:PAY_CODE] != 'DAILY_SCHEDULE_HOURS' then 1 else 0 endif

[Luke_C]

That syntax looks correct, I'm not following if you have a question or issue? 

[DanielCarro]

Credit should be 0 as it is a pair

[Luke_C]

Credit is showing as 0 with the sample data provided, can you give the specific example in the context of the original dataset? Where is it wrong and what is the expected value.