Script : Changing old value with new value

[Anonymous]

Hi,

I'm stuck on my script...

My problem :

- I have a table which contain every transaction for my accounts. Each transaction is link with a date, an account and a sales.

- For several accounts sales are changing every year

>> So I would like to :

1° - update in my script "Sales" with the last sales linked. In my example I would like to change "Rémi" to "Luc"

2° - create a row based on "Date" with an if statement >> if (date < '201810', 'Budget1','Budget2')

Date	Account	Sales
201701	A	Rémi
201702	A	Rémi
201810	A	Luc

How can I do this ? I'm stuck...

I've tried to work with wildmatch function for the first issue, and temp table for the second but I always get an error...

Thanks a lot for your help !

Rémi

[jensmunnichs]

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.