Something like this (PFA)?
Some assumptions made:
- Wasn't entirely sure on your first requirement, I think you want every account to show 'Sales' as the most recent Sales? So if another line was added to your data that has account A and 'Bob' as Sales and a more recent date, Sales will change to 'Bob' for every previous record?
- I assumed that you wanted the first requirement grouped by Account, so another account 'B' can be added with a different name in 'Sales' and a more recent Date and it will have no effect on Account A.
- I also assumed you actually wanted your if to return the values 'Budget1' and 'Budget2', and that these aren't fields in your source data.
So with all those assumptions in mind, I started with this data. I added a few lines to differentiate between accounts:
Temp:
LOAD * INLINE [
Date, Account, Sales
201701, A, Rémi
201702, A, Rémi
201810, A, Luc
201704, B, Bob
201803, B, Bob
201804, B, John];
I then loaded in the same data, adding your second requirement and sorting the table first by account ascending, and date descending, to have the max date on top for every account. This cannot be done while loading in the initial data because the fields have to be loaded in before they can be used in calculations etc.
Final:
LOAD
*,
if(Date < 201810, 'Budget1','Budget2') as Budget
Resident Temp
Order by Account asc, Date desc;
I then took the first value of Sales, grouped by account, to get the most recent Sales name for every account. I then join this table to the rest of the data:
Left join
LOAD
Account,
FirstValue(Sales) as Sales2
Resident Final
Group by Account;
Finally, we have to drop the temp table and (optionally) drop the old Sales field:
Drop table Temp;
//Drop field Sales; //Uncomment to remove the old Sales field
The final result looks like this:
I hope this fits your requirements, if not, let me know.