Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Discarding rows based on combination of values

NikiJF
7 - Meteor

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 Discard
9860IFRS16 Rent Payables23/06/2020 44,000.00Select
9860Rent Payables01/11/2018 3,875.00Discard
12436IFRS16 Rent Payables24/06/2019 111,450.00Select
12436Rent Payables25/03/2010 111,450.00Discard
36368IFRS16 Rent Payables27/10/2021 116,749.99Discard
36368IFRS16 Rent Payables28/11/2022 144,250.00Select
36368Rent Payables28/08/2011 104,750.00Discard
115912IFRS16 Rent Payables24/06/2019 1,175,000.00Select
115912Rent Payables29/09/2014 901,000.00Discard
137972IFRS16 Rent Payables24/06/201924/12/20222,640.00Discard
137972IFRS16 Rent Refund22/11/202224/12/20221,616.52Discard
137972Rent Payables22/11/202224/12/20221,616.52Select
137976Rent Payables22/11/2018 0.00Discard
137976Rent Payables22/11/202229/12/20229,879.18Select
148760Rent Payables27/11/2014 119,600.00Discard
148760Rent Payables27/11/202224/12/202272,893.82Select
157948Rent Payables31/03/2013 0.00Select
158460IFRS16 Rent Payables01/04/2019 4,110.21Discard
158460Rent Payables01/10/2021 17,806.89Discard
158460Rent Refund29/09/2022 17,806.92Select
158468IFRS16 Rent Payables01/04/2019 13,400.00Select
158468Rent Payables01/07/2014 0.00Discard
158472IFRS16 Rent Payables28/11/202231/12/202211,641.32Select
158472Rent Payables28/05/2014 0.00Discard
158504IFRS16 Rent Payables01/04/2019 2,885.27Discard
158504Rent Payables01/10/2021 12,500.00Discard
158504Rent Refund20/09/202224/12/20229,708.90Select
174452IFRS16 Rent Payables29/09/2020 120,900.00Select
195700IFRS16 Rent Payables24/06/2022 145,200.00Select
197024IFRS16 Rent Payables28/05/2019 5,000.00Discard
197024IFRS16 Rent Payables28/11/2022 5,895.93Select
197024Rent Payables09/01/2019 0.00Discard
200608IFRS16 Rent Payables24/06/2022 10,400.00Select
200608IFRS16 Rent Refund27/02/2020 9,400.00Discard
200608Rent Payables01/03/2019 0.00Discard
200672IFRS16 Rent Payables22/01/2022 2,400.00Select
200672Rent Payables22/01/202221/01/20232,400.00Discard
200672Rent Refund22/01/202221/01/20232,400.00Discard
209832IFRS16 Rent Payables20/01/2021 120,000.00Select
7 REPLIES 7
FreeRangeDingo
11 - Bolide
11 - Bolide

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. 

FreeRangeDingo_0-1669649211187.png

 

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. 

 

MichelleMitchellLutz_0-1669649970698.pngMichelleMitchellLutz_1-1669649998538.png

 

MichelleMitchellLutz_2-1669650012679.png

MichelleMitchellLutz_3-1669650033569.pngMichelleMitchellLutz_4-1669650046874.png

 

 

NikiJF
7 - Meteor

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
7 - Meteor

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
11 - Bolide
11 - Bolide

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
7 - Meteor

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

FreeRangeDingo
11 - Bolide
11 - Bolide

@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.

 

FreeRangeDingo_0-1669651869341.png

 

Labels