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 NoCondition TypeEffective FromValid toAmountSelect or Discard9860IFRS16 Rent Payables23/06/2020 44,000.00Select9860Rent Payables01/11/2018 3,875.00Discard12436IFRS16 Rent Payables24/06/2019 111,450.00Select12436Rent Payables25/03/2010 111,450.00Discard36368IFRS16 Rent Payables27/10/2021 116,749.99Discard36368IFRS16 Rent Payables28/11/2022 144,250.00Select36368Rent Payables28/08/2011 104,750.00Discard115912IFRS16 Rent Payables24/06/2019 1,175,000.00Select115912Rent Payables29/09/2014 901,000.00Discard137972IFRS16 Rent Payables24/06/201924/12/20222,640.00Discard137972IFRS16 Rent Refund22/11/202224/12/20221,616.52Discard137972Rent Payables22/11/202224/12/20221,616.52Select137976Rent Payables22/11/2018 0.00Discard137976Rent Payables22/11/202229/12/20229,879.18Select148760Rent Payables27/11/2014 119,600.00Discard148760Rent Payables27/11/202224/12/202272,893.82Select157948Rent Payables31/03/2013 0.00Select158460IFRS16 Rent Payables01/04/2019 4,110.21Discard158460Rent Payables01/10/2021 17,806.89Discard158460Rent Refund29/09/2022 17,806.92Select158468IFRS16 Rent Payables01/04/2019 13,400.00Select158468Rent Payables01/07/2014 0.00Discard158472IFRS16 Rent Payables28/11/202231/12/202211,641.32Select158472Rent Payables28/05/2014 0.00Discard158504IFRS16 Rent Payables01/04/2019 2,885.27Discard158504Rent Payables01/10/2021 12,500.00Discard158504Rent Refund20/09/202224/12/20229,708.90Select174452IFRS16 Rent Payables29/09/2020 120,900.00Select195700IFRS16 Rent Payables24/06/2022 145,200.00Select197024IFRS16 Rent Payables28/05/2019 5,000.00Discard197024IFRS16 Rent Payables28/11/2022 5,895.93Select197024Rent Payables09/01/2019 0.00Discard200608IFRS16 Rent Payables24/06/2022 10,400.00Select200608IFRS16 Rent Refund27/02/2020 9,400.00Discard200608Rent Payables01/03/2019 0.00Discard200672IFRS16 Rent Payables22/01/2022 2,400.00Select200672Rent Payables22/01/202221/01/20232,400.00Discard200672Rent Refund22/01/202221/01/20232,400.00Discard209832IFRS16 Rent Payables20/01/2021 120,000.00Select Data.xlsx

Discarding rows based on combination of values

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)

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

Data.xlsx

Workflow

Datasets

Developer

Help

Accepted answers

MichelleMitchellLutz

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.

Discarding rows based on combination of values - Solution MML.yxmd

All comments

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

Hi!

Discarding rows based on combination of values - Solution MML.yxmd

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.

Community11-28-22.yxmd

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.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703