Alteryx Filter and Join

[Sangz]

I have a table below. I need to get an output of all the Recipients where Agreement Status <> Current Agreement and only if that Recipient did not have atleast 1 Current Agreement.  

Recipient	Agreement Status	Agreement Number
12345	Past Agreement	99999999
12345	Current Agreement	88888888
12345	Past Agreement	77777777
67890	Past Agreeement	66666666
67890	Past Agreement	55555555

Hence the below output should be only the below (because Recipient 12345 had 1 Current Agreement):

Recipient	Agreement Status	Agreement Number
67890	Past Agreeement	66666666
67890	Past Agreement	55555555

I tried it by using  Left Join on the True and False of the applied Filter, but this seems to not show me Recipient 67890 at all. Please help

[Emil_Kos]

Hi @Sangz 

i dont have access to my PC but did you checked If you dont have any extra spaces.

maybe you need to use cleansing tool to make the data in line with each other.

[Sangz]

@emil_kos,

The Past Agreement / Current Agreement is anew vaiable that is handled in the formula, so no chances for an extra space to come in right? Is there something wrong in the logic applied of the Join?

[danilang]

Hi @Sangz 

You can accomplish it like this. 

Count number of each kind of status for each recipient.  Use a filter to find the recipients with current agreements.  Join to your input data on Recipient and take the L output of the join, since you want to exclude those with a current status

BTW.  @Emil_Kos was correct, even without his computer!. In the words of Darth Vader "Impressive...Most Impressive"  Your table has "Current Agreement" with a space and the filter is [AgreementFlag] != "CurrentAgreement"(No Space).  Adding the space in the filter criteria, makes the logic work for your sample data

Dan

[Emil_Kos]

Hi @danilang,

my first check when the join isn't working is making sure that they are all in the same format 😀

This time it was related to formula and it is funny that it was at least partially right. 

[Sangz]

Thank you @Emil_kos & @danilang , However that was a typo when i typed my query, i can confirm that my table has no space in CurrentAgreement. Im unable to understand why the left join is not working as expected. Any other tips you can provide me please?

I need to check if a Recipient has a missing Current Agreement in place. What i am doing is attached in the workflow.

ecipient	Agreement Status	Agreement Number
12345	PastAgreement	99999999
12345	CurrentAgreement	88888888
12345	PastAgreement	77777777
67890	PastAgreeement	66666666
67890	PastAgreement	55555555
67890	FutureAgreement	44444444

Hence the below output should be only the below (because Recipient 12345 had 1 Current Agreement):

Recipient	Agreement Status	Agreement Number
67890	PastAgreeement	66666666
67890	PastAgreement	55555555

I tried it by using  Left Join on the True and False of the applied Filter, but this seems to not show me Recipient 67890 at all. 

• Developer

[Sangz]

I need to check if a Recipient has a missing Current Agreement in place. What i am doing is attached in the workflow.

ecipient	Agreement Status	Agreement Number
12345	PastAgreement	99999999
12345	CurrentAgreement	88888888
12345	PastAgreement	77777777
67890	PastAgreeement	66666666
67890	PastAgreement	55555555
67890	FutureAgreement	44444444

Hence the below output should be only the below (because Recipient 12345 had 1 Current Agreement):

Recipient	Agreement Status	Agreement Number
67890	PastAgreeement	66666666
67890	PastAgreement	55555555

I tried it by using  Left Join on the True and False of the applied Filter, but this seems to not show me Recipient 67890 at all. 

[Emil_Kos]

Hi @Sangz,

I think the output from your data should be:

You are attaching a workflow but it will not work for us as we don't have the underlying data.

The only difference between the solution that was sent by @danilang is the way that you connect the data:

If this will not help can you send us this workflow with sample data inside thanks to that it would work for us and we could take a look how what is the root cause of it. 

[Emil_Kos]

Hi @Sangz,

Please also use the data cleansing tool on the recipient column as I have a feeling one of the positions might have some extra space etc

Copy-paste the data to excel and try to do a vlookup. Check if it will work. 

[Sangz]

Thank you @Emil_Kos for the suggestion. Yea it makes sense what you have suggested. I have tried it out and seems to look promising. But going to do some more checks with a manual pivot to compare my result. Thanks for the swift reply! will keep you posted.

Thanks @danilang for your suggestions and quite support too! Much appreciaied.

[Emil_Kos]

Hi @Sangz,

if you need anything else please let me know!