Another Kettle sample

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:

  • empty the etl_log file to clean out any old stuff
  • create a entry in the etl_log for the user 'sys' - who will be the author of the alerts
  • for each top 500 customer create an entry in the etl_log
  • both the above steps are necessary so that in the OpenVPMS Loader step we can create the required links to the author and customer.

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.

Syndicate content