Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

New Contributor

If a field is null use data from other field

Hi, I created a Qlikview script to pull through some shipment information. It contains fields such as Shipper Country, City and Postcode etc. Since making this, another field has appeared in our source data called Pickup Shipper Country, City and Postcode. 

I would like my script to replace the data in fields Shipper Country, City and Postcode if there is another value present in the Pickup fields. 

I am new to Qlikview so would really appreciate some guidance.

Script is below.

// Load the Matrix SO report (granularity: package)
// Apply naming conventions to field names
// Calculate Tradelane and LaneID


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

[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 Country Name],
[Pick Up Location Country Code],
[Pick Up Location City],
[Pick Up Location Postal Code],

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

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

(biff, embedded labels, table is Results$);

1 Reply

Re: If a field is null use data from other field

It would be more helpful if you can attach the qvw file, so folks can look at the data model you have etc. as well.  

That being said, have a look in the Design Blog area of the Community, below is the main link, you can use the search box to search for posts with examples that may help you out here.



To help users find verified answers, please don't forget to mark a correct resolution or answer to your problem or question as correct.