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

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

Developer

Accepted answers

All comments

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

New Workflow6.yxmd

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.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703