Field Not Found Error

[DanielReed]

Hi, I keep getting the 'Field Not Found Error - Field ShipperCountryCode Not Found' when I run the below script. I have used the If(len) formula as I wish to replace the data in a field if another field contains a value. Any ideas?

//===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],

// For each country, the tradelane identifier has to be entered in the input file
applymap('Tradelane_map', ShipperCountryCode, '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




FROM
[Input\Shipping_Order_Extract-week 27.xlsx.xls]
(biff, embedded labels, table is Results$);

[jaibau1993 · Accepted solution]

Hi!

It is because "ShipperPostalCode" is not defined when the load statement is run. When you run the Load, the field that exists is "[Shipper Postal Code]" (with blanck spaces) and, then you define ShipperPostalCode as 

if(len([Pick Up Location Country Code])>0,[Pick Up Location Country Code], [Shipper Country Code])

Said so, try something like

LOAD

[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],

// For each country, the tradelane identifier has to be entered in the input file
applymap('Tradelane_map', ShipperCountryCode, '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 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 Code])>0,[Pick Up Location Country Code], [Shipper Country Code]) as ShipperCountryCode

FROM
[Input\Shipping_Order_Extract-week 27.xlsx.xls]
(biff, embedded labels, table is Results$);

[DanielReed]

Thank you, that part of the script now works however i get another field not found error on the next step of my script even though i have kept my field names consistent?

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;

[jaibau1993]

Which field is not found this time?

[DanielReed]

ShipperCountry even though the field name is the same in the script above.

[jaibau1993]

This time you are loading the table from a resident one named "Packages_loaded". If the field ShipperCountry  is not found  it is because ShipperCountry  does not exist in Packages_loaded. Check the content of Packages_loaded and make sure ShipperCountry is there!

[DanielReed]

The script doesn't run if I comment out the below. If I leave it uncommented then the script runs however a bit further down the script I do a concatenation of ShipperCountryCode and ShipperPostalCode but what it is pulling is the data from the field [Shipper Country Code] and [Shipper Postal Code] rather than what should be there when the IF(LEN) is loaded?

// [Shipper Country Name] as ShipperCountry,
// [Shipper Country Code] as ShipperCountryCode,
// [Shipper City] as ShipperCity,

// 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,

[DanielReed]

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;

[DanielReed]

Any suggestions?

[jaibau1993]

Hi!

What is the exact error you get?

I see several weird things:

1) In Packages_loaded you do the following (summarized)

Packages_loaded:
LOAD
     [Shipper Country Name] as ShipperCountry,
     [Shipper Country Code] as ShipperCountryCode
;
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
FROM
[Input\Shipping_Order_Extract*.xls]
(biff, embedded labels, table is Results$);

First you create a new "ShipperCountry" but then you load the original one in Shipper Country Name] as ShipperCountry so you have done nothing

2) I don't understand why CountryShipper is not found. Are you sure that the error is "countryShipper not found"? Try adding a NoConcatenate to Packages_With_LaneID

Packages_With_LaneID:
NoConcatenate Load *, ...

Regards,

Jaime.