In this post I will examine the “Transaction Report.xlsx” file and will determine which columns with data I’m going to use to create the chart.
This excel file contains these columns:
- Status – contains values of Clean, Reversed, Rejected, Unknown. Will not use.
- Version – has values of T1, T2, T3 depending on transaction. Will not use.
- Transaction Type – seems to contain several values only. I’m interested in those that have a value of “Transfer IN”. Will use.
- Docket No. – completely empty column.
- Ref 1 – most rows contain a number. Will not use.
- UMI – every row contains a 14 digit number. Seems like a transaction ID to me. Will not use.
- Location ID – my companies ID on CHEP system. Will not use.
- Location – in this case this is my companies name. All column filled with the same value. Will not use.
- Other Party ID – suppliers ID on CHEP system. will not use.
- Other Party – Name of the supplier, who used CHEP pallets for delivery. Will use.
- Other Party Country – in this case the whole column is filled with United Kingdom value. Will not use.
- Equipment Code – seems like every pallet type has a code. Will not use.
- Equipment – contains data about pallet type. Useful but will not use.
- Quantity – number of pallets delivered. Will use.
- Ref – inconsistent data (names, PO numbers). Will not use.
- Other Ref – inconsistent data, some rows are empty. Will not use.
- Batch Ref – seems to contain inconsistent data that I don’t find useful
- Shipment Date – date of shipment. Will use this date as delivery date.
- Delivery Date – it is always the same as shipment date, however there several rows that are empty.
- Effective Date – no data in this column.
- Create Date – not sure what this date is, but it is always later date then delivery. Will not use.
- Created By – filled with text. Will not use.
- Invoice No. – no data in this column.
- Reason – most of the rows are empty, with just couple of them filled. Will not use.
- Data Source – filled with text, which is not useful for my project.
So after looking into this file, I will need to create a database with 4 columns in order to perform SQL queries and arrange the data in order that I need.
- Transaction Type (varchar)
- Other Party (varchar)
- Quantity (int)
- Shipment Date (date)
At least this part doesn’t seem complicated 😉