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
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
Hi Luke, can you combine two if statements in the multi-row formula?
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.
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
That syntax looks correct, I'm not following if you have a question or issue?
Credit should be 0 as it is a pair
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.