Whole script below.
// Load the Matrix SO report (granularity: package)
// Apply naming conventions to field names
// Calculate Tradelane and LaneID
Directory;
Packages_loaded:
LOAD
//===SHIPPING ORDER===
[Shipping Order Number] as MatrixSO,
[Port of Loading] as PoL,
[Port of Discharge] as PoD,
date(floor([Expected Ship Date])) as ExpShipDate,
Upper([Service Level]) as ServiceLevel,
[Path Type] as MatrixPathType,
INCOTerms,
//===SHIPPER===
[Shipper Name] as ShipperName,
[Shipper Alias] as ShipperAlias,
[Shipper Country Name] as ShipperCountry,
[Shipper Country Code] as ShipperCountryCode,
[Shipper City] as ShipperCity,
[Shipper State] as ShipperState,
[Shipper Postal Code] as ShipperPostalCode,
[Shipper Region Name] as ShipperRegion,
// [Pick Up Location Name],
// [Pick Up Location Alias],
// [Pick Up Location Country Name],
// [Pick Up Location Country Code],
// [Pick Up Location City],
// [Pick Up Location State],
// [Pick Up Location Postal Code],
// [Pickup Location Region Name],
//===DESTINATION===
[Delivery Location Name] as DeliveryName,
[Delivery Location Alias] as DeliveryAlias,
[Delivery Location Country Name] as DeliveryCountry,
[Delivery Location Country Code] as DeliveryCountryCode,
[Delivery Location City] as DeliveryCity,
[Delivery Location State] as DeliveryState,
[Delivery Location Postal Code] as DeliveryPostalCode,
[Delivery Location Region Name] as DeliveryRegion,
//===PACKAGES===
PO as PO,
[Order Type] as OrderType,
[Release Number] as ShipperReleaseNumber,
[Customer Cost Center] as CustomerCostCenter,
[Package Type] as PackageType,
[Part Number] as PartNumber,
[Hazardous/ DG] as DG,
[Hazmat/UN Number] as UN_Number,
[Associated Quantity] as m.package_qty,
nummax([Package Length], [Package Width]) as m.package_L,
nummin([Package Length], [Package Width]) as m.package_W,
[Package Height] as m.package_H,
//[Package Cube] as m.package_cbm, Not always correct. Therefore calculated below from dimensions
[Package Weight] as m.package_kg,
// Determine tradelane
// For each country, the tradelane identifier has to be entered in the input file
applymap('Tradelane_map', [Shipper Country Code], 'n/a')
& ' to ' &
applymap('Tradelane_map', [Delivery Location Country Code], 'n/a') as Tradelane,
// For non-UK origins and destinations, the rates are stored without any characters (eg. not '5469 VV' but '5469').
// In order to define the correct %RateID (see below), we need to purge the shipper and delivery postal codes
// outside UK from any non-numeric characters
IF(ShipperCountryCode = 'GBR',
ShipperPostalCode,
KeepChar(ShipperPostalCode, '0123456789')) as ShipperPostalCode_purged,
IF([Delivery Location Country Code] = 'GBR',
[Delivery Location Postal Code],
KeepChar([Delivery Location Postal Code], '0123456789')) as DeliveryPostalCode_purged;
// if(len([Pick Up Location Country Name])>0,[Pick Up Location Country Name], [Shipper Country Name]) as ShipperCountry,
// if(len([Pick Up Location Country Code])>0,[Pick Up Location Country Code], [Shipper Country Code]) as ShipperCountryCode,
// if(len([Pick Up Location City])>0, [Pick Up Location City], [Shipper City]) as ShipperCity,
// if(len([Pick Up Location Postal Code])>0, [Pick Up Location Postal Code], [Shipper Postal Code]) as ShipperPostalCode;
LOAD
*,
if(len([Pick Up Location Country Name])>0,[Pick Up Location Country Name], [Shipper Country Name]) as ShipperCountry,
if(len([Pick Up Location Country Code])>0,[Pick Up Location Country Code], [Shipper Country Code]) as ShipperCountryCode,
if(len([Pick Up Location City])>0, [Pick Up Location City], [Shipper City]) as ShipperCity,
if(len([Pick Up Location Postal Code])>0, [Pick Up Location Postal Code], [Shipper Postal Code]) as ShipperPostalCode
FROM
[Input\Shipping_Order_Extract*.xls]
(biff, embedded labels, table is Results$);
// Calculate LaneID (formula created by *) -->CHECK WHETHER THIS IS CORRECT!!!
//Furthermore see whether the country code can be used instead of 'XX-'. This will eliminate dupplicates.
Packages_With_LaneID:
Load *,
trim(if(len(if(IF(upper(TRIM(ShipperCountry))='UNITED KINGDOM','XX-'&
IF(not IsNum(MID(ShipperPostalCode_purged,2,1)),UPPER(LEFT(ShipperPostalCode_purged,4)),
UPPER(LEFT(ShipperPostalCode_purged,3))),'')='',
if(IF(upper(TRIM(ShipperCountry))='UNITED STATES','xx-'&
IF(LEN(ShipperPostalCode_purged)=4,'0'&ShipperPostalCode_purged,LEFT(ShipperPostalCode_purged,5)),'')='',
if(IF(UPPER(TRIM(ShipperCountry))='CANADA','XX-'&
left(ShipperPostalCode_purged,3),'')='',if(Len(ShipperPostalCode_purged)<2,'MISSING DATA',
'XX-'&ShipperPostalCode_purged),'XX-'&left(ShipperPostalCode_purged,3)),
IF(upper(TRIM(ShipperCountry))='UNITED STATES','XX-'&
IF(LEN(ShipperPostalCode_purged)=4,'0'&ShipperPostalCode_purged,LEFT(ShipperPostalCode_purged,5)))),
IF(upper(TRIM(ShipperCountry))='UNITED KINGDOM','XX-'&
IF(not IsNum(MID(ShipperPostalCode_purged,2,1)),UPPER(LEFT(ShipperPostalCode_purged,4)),
UPPER(LEFT(ShipperPostalCode_purged,3))))))<5,'MISSING DATA',
if(IF(upper(TRIM(ShipperCountry))='UNITED KINGDOM','XX-'&
IF(not IsNum(MID(ShipperPostalCode_purged,2,1)),UPPER(LEFT(ShipperPostalCode_purged,4)),
UPPER(LEFT(ShipperPostalCode_purged,3))),'')='',
if(IF(upper(TRIM(ShipperCountry))='UNITED STATES',
'xx-'&IF(LEN(ShipperPostalCode_purged)=4,'0'&ShipperPostalCode_purged,LEFT(ShipperPostalCode_purged,5)),'')='',
if(IF(UPPER(TRIM(ShipperCountry))='CANADA','XX-'&
left(ShipperPostalCode_purged,3),'')='',if(Len(ShipperPostalCode_purged)<2,'MISSING DATA',
'XX-'&ShipperPostalCode_purged),'XX-'&left(ShipperPostalCode_purged,3)),
IF(upper(TRIM(ShipperCountry))='UNITED STATES','XX-'&
IF(LEN(ShipperPostalCode_purged)=4,'0'&ShipperPostalCode_purged,LEFT(ShipperPostalCode_purged,5)))),
IF(upper(TRIM(ShipperCountry))='UNITED KINGDOM','XX-'&
IF(not IsNum(MID(ShipperPostalCode_purged,2,1)),UPPER(LEFT(ShipperPostalCode_purged,4)),
UPPER(LEFT(ShipperPostalCode_purged,3)))))))
&'-'&
trim(if(len(if(IF(upper(TRIM(DeliveryCountry))='UNITED KINGDOM','XX-'&
IF(not IsNum(MID(DeliveryPostalCode_purged,2,1)),UPPER(LEFT(DeliveryPostalCode_purged,4)),
UPPER(LEFT(DeliveryPostalCode_purged,3))),'')='',
if(IF(upper(TRIM(DeliveryCountry))='UNITED STATES','xx-'&
IF(LEN(DeliveryPostalCode_purged)=4,'0'&DeliveryPostalCode_purged,LEFT(DeliveryPostalCode_purged,5)),'')='',
if(IF(UPPER(TRIM(DeliveryCountry))='CANADA','XX-'&
left(DeliveryPostalCode_purged,3),'')='',if(Len(DeliveryPostalCode_purged)<2,'MISSING DATA',
'XX-'&DeliveryPostalCode_purged),'XX-'&left(DeliveryPostalCode_purged,3)),
IF(upper(TRIM(DeliveryCountry))='UNITED STATES','XX-'&
IF(LEN(DeliveryPostalCode_purged)=4,'0'&DeliveryPostalCode_purged,LEFT(DeliveryPostalCode_purged,5)))),
IF(upper(TRIM(DeliveryCountry))='UNITED KINGDOM','XX-'&
IF(not IsNum(MID(DeliveryPostalCode_purged,2,1)),UPPER(LEFT(DeliveryPostalCode_purged,4)),
UPPER(LEFT(DeliveryPostalCode_purged,3))))))<5,'MISSING DATA',
if(IF(upper(TRIM(DeliveryCountry))='UNITED KINGDOM','XX-'&
IF(not IsNum(MID(DeliveryPostalCode_purged,2,1)),UPPER(LEFT(DeliveryPostalCode_purged,4)),
UPPER(LEFT(DeliveryPostalCode_purged,3))),'')='',
if(IF(upper(TRIM(DeliveryCountry))='UNITED STATES',
'xx-'&IF(LEN(DeliveryPostalCode_purged)=4,'0'&DeliveryPostalCode_purged,LEFT(DeliveryPostalCode_purged,5)),'')='',
if(IF(UPPER(TRIM(DeliveryCountry))='CANADA','XX-'&
left(DeliveryPostalCode_purged,3),'')='',if(Len(DeliveryPostalCode_purged)<2,'MISSING DATA',
'XX-'&DeliveryPostalCode_purged),'XX-'&left(DeliveryPostalCode_purged,3)),
IF(upper(TRIM(DeliveryCountry))='UNITED STATES','XX-'&
IF(LEN(DeliveryPostalCode_purged)=4,'0'&DeliveryPostalCode_purged,LEFT(DeliveryPostalCode_purged,5)))),
IF(upper(TRIM(DeliveryCountry))='UNITED KINGDOM','XX-'&
IF(not IsNum(MID(DeliveryPostalCode_purged,2,1)),UPPER(LEFT(DeliveryPostalCode_purged,4)),
UPPER(LEFT(DeliveryPostalCode_purged,3)))))))
as LaneID
Resident Packages_loaded;
DROP Table Packages_loaded;
DROP Fields ShipperPostalCode_purged, DeliveryPostalCode_purged;
//===ENRICH PACKAGES WITH INFORMATION===
Packages_enriched:
NoConcatenate Load *,
// The InvoiceLineID is the level at which is invoiced.
// Therefore this statement is key in the logic!
// The old Excel billing sheet did not include ServiceLevel and DeliveryAlias
Upper(floor(ExpShipDate) & '|' &
ShipperAlias & '|' & ShipperCountryCode & '-' & ShipperPostalCode & '|' &
DeliveryAlias & '|' & DeliveryCountryCode & '-' & DeliveryPostalCode & '|' &
ServiceLevel) as InvoiceLineID,
// Volume in input file is not always calculated correctly. Therefore it is calculated from dimensions.
m.package_L/100 * m.package_W/100 * m.package_H/100 as m.package_cbm,
// Calculate loadmeter if not stacked.
// Width is maximized on 240 cm in order not to overestimate in case of oversized cargo
m.package_L/100 * nummin(m.package_W, 240)/100 / 2.4 as m.package_ldm_floor,
// Indicator whether the package is out-of-gauge (1=OoG). If OoG then update the service level for the scope check
if((m.package_L>240 and m.package_W>240) or
m.package_H>240, 1,0) as ind.package_oversized,
// Overwrite ServiceLevel of the package with SPECIAL if the package is oversized. This assumes that if on the same day on the same lane there is a PREMIUM shipment with
// both in gauge and out of gauge packages, it is split in two different shipments: one PREMIUM and one SPECIAL.
if(((m.package_L>240 and m.package_W>240) or m.package_H>240) and // Oversized if either length and/or width or height exceeds standard truck dimensions
ServiceLevel = 'PREMIUM', // Do not override ServiceLevel if not PREMIUM. Sometimes dimensions are entered in mm (error)
'SPECIAL',
ServiceLevel) as ServiceLevel_for_scope_check
Resident Packages_With_LaneID;
DROP Table Packages_With_LaneID;
//===SELECT SHIPMENTS IN SCOPE UK GROUND BILLING REPORT AND
// ORDER TABLE FOR LOADMETER CALCULATION
// Indicator showing whether the tradelane is in scope of this billing sheet
// By default the tradelane is in scope. In the inputfile, list the tradelanes that are out of scope.
Packages_with_scope_indicator:
NoConcatenate lOAD *,
applymap('GroundBillingScope', Tradelane&'|'&ServiceLevel_for_scope_check, 'check') as indInScope
Resident Packages_enriched;
DROP Table Packages_enriched;
DROP Field ServiceLevel_for_scope_check;
// Filter out shippers not in scope
Packages_inscope:
NoConcatenate
Load *, RowNo() as PackageID
Resident Packages_with_scope_indicator
Where $(Include=Configuration\Scope_Selection_Statement.txt) //14-5-2019: WHERE-clause moved to external fil in Config-directory
Order by InvoiceLineID, m.package_ldm_floor, m.package_H;
DROP Table Packages_with_scope_indicator;
