CHEP Pallets Report

CHEP PALLETS REPORT (flickerbook.co.uk) is a web project that I developed while working as a logistics administrator. I was asked to have a look at the CHEP pallet transfers and see if I can find a way to identify unusual transfers or a way to compare the amount of CHEP pallets that come to our site and the amount that leaves to identify discrepancies. After some consideration (you can read about it in this post CHEP Pallets Monitoring And Report. Step 1 (machinecandream.co.uk) ) I decided to use the “Transaction Report” excel file from CHEP – myCHEP website and use it to render charts that help identify unusual transfers. So this website works like a data analysis dashboard.

How it works

This web app simply allows you to upload an excel file that contains transaction details, and using the data in this file it renders 4 charts that shows:

  • Total pallets transferred IN and OUT for the time period.
  • Total pallet transfers grouped by transfer type for time period.
  • Monthly pallets transferred by every supplier for the time period.
  • Monthly pallet transfers by transfer type.

Once the excel file gets uploaded, using JavaScript I extract the data from these columns:

  • Transaction Type (varchar)
  • Other Party (varchar)
  • Quantity (int)
  • Shipment Date (date)

I then store this data in a database, and using SQL queries like GROUP BY and SUM() retrieve the necessary data and arrange it in PHP multidimensional arrays and send it to front end as JSON for the charts to be rendered.

Problems CHEP Pallets report helped to solve

This dashboard helped to identify unusual transfers. For example I’ve uploaded excel file that contained one year worth of transfers and I did spotted one unusual bar chart, demonstrated in the picture below.

Unusual transfer.

This chart is very unusual as it means that approximately 130 pallets were delivered every day in December 2022. That is 5 full lorries a day (26 pallets per trailer). We do not have such a supplier who delivers 5 full loads every day.

I investigated the uploaded Excel file, and noticed that all transfers were created on 2022-12-31, which most likely means that supplier wanted to get it’s accounts right before new year.

Unusual transfers.

After looking at the “transfers by type” chart, I can see that there is a high number of “Reversed Transfers IN” transfers for this time period of 1 year.

chart of 4705 “Transfer IN” pallets.

After sorting excel file by “Reversed Transfer IN” column, I found that most of these reversed transfers were done by the same supplier on 2023-01-23.

Unusual transfers rectified.

These reverse transfers are identical to the incorrect transfers done, so it means that this issue was picked up and sorted. In one way this shows that these charts I created are helpful to identify unusual transfers, however if they have been rectified, it does not display it. I had to investigate the excel file with transfers data to figure out this has been rectified. However if this chart was used at 1st of January 2023 it would have picked up the incorrect transfers and that was the whole purpose of this project.

What I learned while developing CHEP pallets report

While developing this project I learned or improved:

  • Tried CSS Flexbox for second time.
  • Improved understanding of SQL GROUP BY QUERY.
  • Created Excel charts.
  • Refreshed my skills using CanvasJS.
  • Improved Organising data into arrays and objects in PHP and JavaScript.
  • Got accustomed using WordPress.

The most beneficial part for me was practising using SQL GROUP BY statement. I now have a couple ideas of how I’m gonna use this on Flickerbook, as for a long time I wanted to have data on earnings grouped by employer or by job position, and this will enable me to do it.

Second beneficial thing is that I wrote more then 20 blog posts on my WordPress page and become familiar with using it. In these posts I explained every step of this project. Thirdly, while blogging I wanted to have sample charts I can display in my posts, so I used Excel to draw them and will now have what to show in my Excel projects page.

Another major benefit was organising data retrieved from database into associative multidimensional arrays and objects. Not everything I done worked out as I intended, but in the end I managed to work it out.

And one more thing, I refreshed my knowledge of using CanvasJS charting library. Haven’t used it since around 2018 as for Flickerbook I used ChartJs, but for this project I deliberately chose CanvasJS in order to try out something different.

All the code and files for this project can be found at this Github.com repository.