We run a ranking system for our customers - the top 5 by sales are classified as Platinum, ranks 6 though 25 as Gold, and 26 through 100 as Silver. We have Platinum, Gold and Silver customer alerts (with appropriate colours).
Although we have a report which lists the current customer ranks and what they should be, our staff were not getting the time to do the updates.
To automate the process I wrote some Kettle stuff that implements the following logic:
For each customer in the top 500 by sales volume over the last 180 days, calculate rank as platimum/gold/silver/none then:
If new rank = old rank
If rank not = none
update alert reason to 'Checked on date/time'
Endif
Exit
Else (ie rank changed)
If old rank not = none
Set end date on existing alert to today, and status to COMPLETED
Endif
Create new plat/gold/silver alert
Endif
The attached zip file (renamed to .txt so I can attach it) contains the job and transforms.
The tricks are:
Even if you don't want to create alerts this code illustrates how to use Kettle to load stuff which references existing data in the system.
Note that I considered writing some SQL to do the complete job, but decided that it was beyond my competance.