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$);

[DanielReed]

Hi Jamie, please see below, It seems I copied the same statement in twice.

The first part of the script runs however when I select to see what has pulled through under 'ShipperPostalCode', it is still the post code that is in that column rather than '[Pick Up Location Postal Code]' ?

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,
// if(len([Pick Up Location Postal Code])>0,[Pick Up Location Postal Code],[Shipper Postal Code]) as ShipperPostalCode,
// [Shipper Postal Code] as ShipperPostalCode,
[Shipper Region Name] as ShipperRegion,
// [Pick Up Location Country Name],
// [Pick Up Location Country Code],
// [Pick Up Location City],
// [Pick Up Location Postal Code],

//===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([Shipper Country Code] = 'GBR',
[Shipper Postal Code],
KeepChar([Shipper Postal Code], '0123456789')) as ShipperPostalCode_purged,

IF([Delivery Location Country Code] = 'GBR',
[Delivery Location Postal Code],
KeepChar([Delivery Location Postal Code], '0123456789')) as DeliveryPostalCode_purged


;
Load
*,


if(len([Pick Up Location Country Code])>0,[Pick Up Location Country Code],[Shipper Country Code]) as ShipperCountryCode,
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

FROM
[Input\Shipping_Order_Extract*.xls]
(biff, embedded labels, table is Results$);

[Brett_Bleess]

In these cases, it is generally best if you can be sure you have the script log enabled, in the Desktop Client, use the Settings\Document Properties\General tab\Generate Logfile checkbox to enable it.  When you run the reload, the script/document log will be in the same location as the .qvw file you are reloading, if you can attach that, it generally makes things easier to troubleshoot at that point.

Here is an Article link as well:

https://support.qlik.com/articles/000002668

Regards,
Brett