Removing Rows of Data

[cengleman]

Hello,

I am trying to match payment rates to payments based on effective dates. I created some formulas and multirow formulas, but my data is not coming out properly. 

Employees can change rates, sometimes 0, 1, 2, 3, or more times throughout the year and I need to ensure that the accurate rate is being multiplied to the payroll qualified compensation, and then that calculation will be compared to the payroll EE contrib % number. I created my first formula to use the first rate and disregard the succeeding rate when appropriate, but I am having trouble trying to remove the preceding rate when only the latter should apply. 

I attached my current workflow; there were 1826 payments, so my end filter should have 1826 true rows not 2121. 

Thanks,

[IraWatt]

Hey @cengleman,

I'm trying to follow the process, could you possibly give a shot example input and what you would expect to have as a result?

Thanks,

Ira

[cengleman]

In row 6, I no longer want the first rate, but I want the second rate applied until the next name appears. Sample text there should be two nulls back to back when the applied rate switches from 1 to 2. Currently, I cannot figure out how to make EE Calc null for the first rate when I no longer want it. This needs to be applied for names/payments with up to 7 rates changes as well. 

[cengleman]

The column with "active' or "historical" is irrelevant, but the TPA election change ID notifies when a rate changes from 1 to 2 or 3 in the dataset. 

[cengleman]

Let me know if you need anymore information

[ddiesel]

Hi @cengleman!

I've had a read through all of the above, and I'm unclear what the logic should be for the calculations. Will you please clarify? I'm sure we can help.

[cengleman]

I provided another marked image and Alteryx file example of what I am trying to create. My goal is to use the nearest previous date and rate applicable for each payment. The rate changes periodically and I want to use the most recent date of rate change prior to the last payment made by the individual. In the example above on row 596, if the date of the payment is 2019-04-26, I want to use the nearest previous rate which was chosen at 2019-04-25 and remove the following two rows since those rates do not apply to this payment/payroll date. Additionally on rows 605-607, the effective date is 2019-05-28 for a payment on 2019-06-07, so I need to remove rows 605 and 606 because those rates are no longer applicable. My data currently contains all rates and dates for transactions, so I am trying to eliminate non-applicable rates/dates for the same transaction. I then multiply my TPA % Deferral rate by the payroll qualified compensation, Payroll EE Calc column, to see if it matches provided data in the row Payroll EE Contrib %. 

[ddiesel]

Hi @cengleman

Thanks for providing more information. Unfortunately, I still cannot follow your logic. Will you please explain the pattern in the sample data you provided?

Once we get the logic all figured out, I think most likely your solution will require an iterative macro to compare two records at a time. I started working one up. I will attach it here if anyone reading this wants to pick up where I left off. Otherwise, I will await more information from you.

Thanks,
Deb

[cengleman]

Hello @ddiesel 

This data set pertains to a 401k plan. Employees select percentage rates or a dollar amount of their paycheck to be put into their 401k account, but they have the option to change their rate at anytime. I am trying to ensure that the correct rate for each pay period is being used. The data in the sample I am trying to "keep" is the applicable rate for the pay period, and the data I am trying to "exclude" or delete entirely are the other rates not applicable for that specific payment, whether the rates were chosen at a future date or if a newer rate is being used. In rows 1-4 on the sample data set, I want to keep row 1 because the first elected rate is being used, and the second rate in row 2 is not yet applicable, so I want it removed. In rows 3 and 4, a new rate has been chosen by the employee effective at 2020-04-17 for the payment 2020-06-30, so I want to remove row 3 because the former rate is no longer applicable. I am trying to filter this data because I need to multiply the accurate rates by qualified employee compensation to ensure the amount pulled from the employees' paycheck matches the new calculation as part of an audit. 

Thank you again for helping! I also apologize for the confusion; I am fairly new to Alteryx and working with the 401k plans so articulating my problem has been difficult.

Best,

Cole