Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Multi-row formula checking opening and closing dates run in sequence

Brinker
8 - Asteroid

Hi,

 

I am trying to check that the transfer of accounts are in sequential order, an account can below to more than 1 person. The problem comes in that there can be multiple transfers to multiple owners, and I need to check that the open date is after the previous close date. If the close date is blank and the status is 'N' it is open.

 

I am having trouble when the transfer happens to more than one person and both accounts are open.

 

IF [Open] < [close]
then 'Pass'
elseif isnull([close])
and [open] > [Row-1:Close]

then 'Pass'
else 'Fail'
endif

5 REPLIES 5
JamesCharnley
13 - Pulsar

Hi @Brinker,

 

Did you attach the right file? I can't seem to see the link between the question you've asked and the rows being generated in the workbook.

Brinker
8 - Asteroid

Thanks @JamesCharnley, here is the correct file.

JamesCharnley
13 - Pulsar

No problem @Brinker.

 

Do you have some kind of example desired output that you can share as well as a comparison? I can't quite follow exactly what the problem is still, and I'm guessing that since nobody has chimed in already then it's proving a little difficult to understand.

 

When you say that account can belong to more than one person, I'm assuming that means ID, but none of the account numbers are duplicated so I'm not tracking where that issue across owners is. Is the number different with different owners? 

 

Also you've said that if the close date is blank and the status is N it's open, but I'm assuming you meant the opposite? Otherwise there's no rows in your example that match that Open description. 

 

If we can clear a couple of the points up I don't think the multi-row should be too hard to configure!

Brinker
8 - Asteroid

Hi @JamesCharnley Let me elaborate a bit ID relates to a product and account is the person, status 'N' is closed and Y is open.

For ID 3, we transfered from 2 people to 2 new people, so we need to check the previous closed date is < than the next opening date. If a row is open like ID 5 it is a valid open record. And ID 4 both closed the account so that is also valid. The problem is when there is more than one new open account. I can't figure the logic out.

Screenshot 2022-11-14 183718.png

Brinker
8 - Asteroid

Not sure if anyone will find it useful but I came to a solution, this code is what I implemented, so it is a bit different from the example dataset I posted, but the logic is there.

if [SOURCEAGREEMENTCODE] = [Row-1:SOURCEAGREEMENTCODE]
and [ACCOUNTSTATUS] = 'N' and [Row-1:ACCOUNTSTATUS] = 'N'
and [Row-1:CLOSEDDATES] < [COMMENCEMENTDATE] then 'PASS'

elseif [SOURCEAGREEMENTCODE] = [Row-1:SOURCEAGREEMENTCODE]
and [ACCOUNTSTATUS] = 'N' and [Row-1:ACCOUNTSTATUS] = 'Y'
and [COMMENCEMENTDATE] > [Row-1:CLOSEDDATES] then 'PASS'

elseif [SOURCEAGREEMENTCODE] = [Row-1:SOURCEAGREEMENTCODE]
and [ACCOUNTSTATUS] = 'Y' and [Row-1:ACCOUNTSTATUS] = 'Y'
and [COMMENCEMENTDATE] > [Row-1:CLOSEDDATES] then 'PASS'

elseif [SOURCEAGREEMENTCODE] = [Row-1:SOURCEAGREEMENTCODE]
and [ACCOUNTSTATUS] = 'N'
and [COMMENCEMENTDATE] = [Row-1:COMMENCEMENTDATE] then 'PASS'

elseif [SOURCEAGREEMENTCODE] != [Row-1:SOURCEAGREEMENTCODE]
and [ACCOUNTSTATUS] = 'N' then 'PASS'

elseif [SOURCEAGREEMENTCODE] != [Row-1:SOURCEAGREEMENTCODE]
and [ACCOUNTSTATUS] = 'Y'
and [COMMENCEMENTDATE] < [CLOSEDDATES] then 'PASS'

else 'FAIL'
endif

Labels