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 Directory; Packages_loaded:
//===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 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
FROM [Input\Shipping_Order_Extract*.xls] (biff, embedded labels, table is Results$);