Overwriting and removing data based upon a generated key.

Question

Here is my problem:

I have 1 year of data, but one of the key criteria, rate per miles, is updated three times during the year. If you can imagine, from January to March we would charge $1.00, from March to September we would charge $1.25 and from September til year-end, we would charge $1.30. Updates were not made so uniformly however (such that the rate changes, and months of change differed). I created a formula which, based on some characteristics of the data, identifies which rows to keep, remove or overwrite.

My dataset would have data points for an April trip at $1.00, despite the rate having changed to $1.25 back in March. I had an identifier in the dataset which called out whether a rate was updated, and, if so, when the update happened. If the contract's start date lines us with the expected start date, the item is given a "Keep" identifier. If an update occurred before the month of accrual for the datapoint, the row should be dropped.

I was able to get rid of all the 'Remove' items with a filter tool, but am unsure how to get rid of the 'Keep' items which are obsolete due to the update.

Does anyone have any ideas?

Scythe80 · Answer

The formula is quite simple, here it is:

IF [policy inception date]=[date] THEN "Keep" ELSEIF [Month_Accrual] >= [date] THEN "Overwrite" ELSE "Remove" ENDIF

Note:

[date] is the date of the contract, so if it was updated, that would be reflected in the date. The inception date is a constant, and represents the start of a policy period. The beginning of the formula acknowledges that if a policy's "update date" is the same as its start date, then no update has happened on that row of data.

Another way I am thinking about this problem is, that if I have two rows of data, where every single criteria is identical EXCEPT for the overwrite identifier, I'd drop the non-overwrite item and keep the overwrite item.

MartWClimber · Answer

Could you share you formula in the post? because without context to the formula (where I assume it goes wrong)  This is a hard question to solve 🙂