Discarding rows based on combination of values

[NikiJF]

Hi, I am trying to select or discard rows based on a combination of values. I'm pretty sure I need a Multi-Row Formula but can't work out the logic.

• Downstream in my workflow is a sort by Ref No then by Effective From then a Unique tool to pick up the Condition Type with the most recent Effective From date, but I have 2 instances in my example data below where this is not happening correctly - Ref No 137972 and 200672.
• My dataset is much larger, I have just included a small subset below.
• For each Ref No, there can be up to 3 Condition Types. 
• I want to create the column called Select or Discard as part of the process

The logic I am looking for is:

If there is an IFRS16 Rent Payables and an IFRS16 Rent Refund Condition Type for the same Ref No AND either the Effective From or Valid to dates are the same, then they cancel each other out and should therefore be discarded, leaving the Rent Payables Condition Type (in the below example this is true for 137972)

OR

If there is a Rent Payables and a Rent Refund Condition Type for the same Ref No AND either the Effective From or Valid to dates are the same, then they cancel each other out and should therefore be discarded, leaving the IFRS16 Rent Payables Condition Type (in the below example this is true for 200672)

How do I build this logic into my workflow please? Thanks in advance for any help.

Ref No	Condition Type	Effective From	Valid to	Amount	Select or Discard
9860	IFRS16 Rent Payables	23/06/2020		44,000.00	Select
9860	Rent Payables	01/11/2018		3,875.00	Discard
12436	IFRS16 Rent Payables	24/06/2019		111,450.00	Select
12436	Rent Payables	25/03/2010		111,450.00	Discard
36368	IFRS16 Rent Payables	27/10/2021		116,749.99	Discard
36368	IFRS16 Rent Payables	28/11/2022		144,250.00	Select
36368	Rent Payables	28/08/2011		104,750.00	Discard
115912	IFRS16 Rent Payables	24/06/2019		1,175,000.00	Select
115912	Rent Payables	29/09/2014		901,000.00	Discard
137972	IFRS16 Rent Payables	24/06/2019	24/12/2022	2,640.00	Discard
137972	IFRS16 Rent Refund	22/11/2022	24/12/2022	1,616.52	Discard
137972	Rent Payables	22/11/2022	24/12/2022	1,616.52	Select
137976	Rent Payables	22/11/2018		0.00	Discard
137976	Rent Payables	22/11/2022	29/12/2022	9,879.18	Select
148760	Rent Payables	27/11/2014		119,600.00	Discard
148760	Rent Payables	27/11/2022	24/12/2022	72,893.82	Select
157948	Rent Payables	31/03/2013		0.00	Select
158460	IFRS16 Rent Payables	01/04/2019		4,110.21	Discard
158460	Rent Payables	01/10/2021		17,806.89	Discard
158460	Rent Refund	29/09/2022		17,806.92	Select
158468	IFRS16 Rent Payables	01/04/2019		13,400.00	Select
158468	Rent Payables	01/07/2014		0.00	Discard
158472	IFRS16 Rent Payables	28/11/2022	31/12/2022	11,641.32	Select
158472	Rent Payables	28/05/2014		0.00	Discard
158504	IFRS16 Rent Payables	01/04/2019		2,885.27	Discard
158504	Rent Payables	01/10/2021		12,500.00	Discard
158504	Rent Refund	20/09/2022	24/12/2022	9,708.90	Select
174452	IFRS16 Rent Payables	29/09/2020		120,900.00	Select
195700	IFRS16 Rent Payables	24/06/2022		145,200.00	Select
197024	IFRS16 Rent Payables	28/05/2019		5,000.00	Discard
197024	IFRS16 Rent Payables	28/11/2022		5,895.93	Select
197024	Rent Payables	09/01/2019		0.00	Discard
200608	IFRS16 Rent Payables	24/06/2022		10,400.00	Select
200608	IFRS16 Rent Refund	27/02/2020		9,400.00	Discard
200608	Rent Payables	01/03/2019		0.00	Discard
200672	IFRS16 Rent Payables	22/01/2022		2,400.00	Select
200672	Rent Payables	22/01/2022	21/01/2023	2,400.00	Discard
200672	Rent Refund	22/01/2022	21/01/2023	2,400.00	Discard
209832	IFRS16 Rent Payables	20/01/2021		120,000.00	Select

[FreeRangeDingo]

I'm working this out but might not have time to finish this morning.  To help with the Condition Type logic, I would use a Summarize to concatenate the values and then join that back to the main data set so you can use it in logic. 

[MichelleMitchellLutz · Accepted solution]

Hi! 

This may be a bit convoluted, but I think it will get the job done. Essentially, I ran both of your logic tests using Cross Tab tools in order to have each record on one row. I then applied the logic test and filtered out records that didn't match. Those matching records are signaled with a Find and Replace and using two Unique tools and a Union (with specific output order) in between did the trick. 

[NikiJF]

Hi @MichelleMitchellLutz, thank you very much for this, I appreciate you coming back so quickly! I will work through it and let you know if I have any questions.

[NikiJF]

Hi @FreeRangeDingo - I am looking forward to seeing more of your proposed solution, as I am not quite sure yet what your next steps are, but I really appreciate you taking the time to work through it for me.

[FreeRangeDingo]

I think I worked out your first chunk of logic largely using summarize tools and rejoining to the data set.  Take a look at this and see if you can get the rest of it using this as an example.

[NikiJF]

Thanks very much @FreeRangeDingo, I will work through this one too and let you know if I have any questions.

[FreeRangeDingo]

@NikiJF in Summarize (20), I meant to use a Count Distinct, not a Count.  It will work if you change it to a Count Distinct.