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. RecipientAgreement StatusAgreement Number12345 Past Agreement9999999912345Current Agreement8888888812345Past Agreement7777777767890Past Agreeement6666666667890Past Agreement55555555 Hence the below output should be only the below (because Recipient 12345 had 1 Current Agreement): RecipientAgreement StatusAgreement Number67890Past Agreeement6666666667890Past Agreement55555555 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 New Workflow2.yxmd

Hi @Sangz, if you need anything else please let me know!

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.

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.

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

New Workflow2.yxmd

Join

Accepted answers

All comments

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

RemoveCurrentAgreements.yxmd

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

New Workflow2.yxmd

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

RithiS 280