How to check multiple values against a single value (Recipient)?

[Sangz]

I need to check if a Recipient has a missing Current Agreement in place. Would pivot be the best solution? Or using formula.

My workflow now provides the output like this. How should i proceed further.

Recipient	Recpt Cust name	AgreementFlag	Count_ofAgreements
12625576	SSSSSSSS	PastAgreement	31
12625627	IIIIIIIIIIIII	CurrentAgreement	182
12625635	MMMM	CurrentAgreement	22
12625635	MMMM	PastAgreement	4
12625645	CCCCCC	CurrentAgreement	5
12625653	DDDDD	CurrentAgreement	1571
12625653	DDDDD	PastAgreement	949
12625681	AAAAAA	CurrentAgreement	7
12625681	AAAAAA	PastAgreement	40
12625681	AAAAAA	FutureAgreement	5
12625702	RRRRRRR	PastAgreement	897

I would like the workflow to provide the output of the Recipient that has no Current Agreement. ie, the output should return the below Recipients based on the above data

Recipient	Recpt Cust name	AgreementFlag	Count_ofAgreements
12625576	SSSSSSSS	PastAgreement	31
12625702	RRRRRRR	PastAgreement	897

[Emil_Kos · Accepted solution]

Hi @Sangz,

I have created a workflow for you:

The desired output:

Please mark my post as a solution if this something helpful for you!

Good Luck!

[Sangz]

Thankyou Emil_Kos for your help. I tried the workflow. Looks like we are almost there, but the Left join is excluding some of the data that i need to see in the output. 

If i do a normal Join, you can see in the below table - Recipeint has both a Past Agreement and Future Agreement, but it does not show up in the workflow using the Left Join. Ideally it should show this Recipeint because there it does not have a "Current Agreement".

What if i have a Recipient that has no Past Agreement Either, then the left Join would fail to show me such a Recipient right? Any suggestions if this is the best possible way to get the data i require? My intention is to ensure that i see all Recipients that do not have a "Current Agreement", even if they had a Past Agr

Recipient	Recpt Cust name	AgreementFlag	Count_RebateAgreement	Recipient	Recpt Cust name	AgreementFlag	Count_RebateAgreement
12626223	FFFFFFF	PastAgreement	56	12626223	FFFFFFF	FutureAgreement	4

[atcodedog05 · Accepted solution]

Hi @Sangz 

The requirement is much more clear now

Here is a workflow for the task.

Output: Recipients which does have CurrentAgreement

Workflow:

Hope this helps 🙂

If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

[Emil_Kos]

Hi @Sangz,

thank you for marking my answer as a solution. 

good luck!

[atcodedog05]

Happy to help 🙂 @Sangz 

Cheers and Happy Analyzing 😀