topic Re: Field Not Found Error in QlikView

Field Not Found Error

DanielReed — Thu, 18 Jul 2019 07:40:51 GMT

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

Re: Field Not Found Error

jaibau1993 — Thu, 18 Jul 2019 08:21:44 GMT

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

Re: Field Not Found Error

DanielReed — Thu, 18 Jul 2019 09:12:41 GMT

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;

Re: Field Not Found Error

jaibau1993 — Thu, 18 Jul 2019 09:44:44 GMT

Which field is not found this time?

Re: Field Not Found Error

DanielReed — Thu, 18 Jul 2019 09:52:27 GMT

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

Re: Field Not Found Error

jaibau1993 — Thu, 18 Jul 2019 11:09:32 GMT

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!

Re: Field Not Found Error

DanielReed — Thu, 18 Jul 2019 11:16:43 GMT

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,

Re: Field Not Found Error

DanielReed — Thu, 18 Jul 2019 11:30:03 GMT

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

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;

Re: Field Not Found Error

DanielReed — Fri, 19 Jul 2019 08:06:13 GMT

Any suggestions?

Re: Field Not Found Error

jaibau1993 — Mon, 22 Jul 2019 06:30:51 GMT

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.

Re: Field Not Found Error

DanielReed — Mon, 22 Jul 2019 08:14:08 GMT

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

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

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

Re: Field Not Found Error

Brett_Bleess — Tue, 13 Aug 2019 13:27:21 GMT

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