Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have an Order table with with two geo id fields, originId and destinationId. Now I want to combine DestinationCity and OriginCity into a dimension in the load statement.
Order:
OrderId | OriginId | DestinationId |
123 | 23 | 44 |
234 | 34 | 65 |
LOAD OrderId,
OriginId,
DestinationId
FROM [lib://AttachedFiles/Orders.xlsx]
(ooxml, embedded labels, table is [Orders]);
DestinationGEO:
LOAD
Id as DestinationId,
City as DestinationCity,
Country as DestinationCountry
FROM [lib://AttachedFiles/Geo.xlsx]
(ooxml, embedded labels, table is [Geo library]);
OriginGEO:
LOAD
DestinationId as OriginId,
DestinationCity as OriginCity,
DestinationCountry as OriginCountry
RESIDENT DestinationGEO;
Here I want to combine the fields:
LOAD OriginCity & ' - ' & DestinationCity as OnD-City ,
OriginCountry & ' - ' & DestinationCountry as OnD-Country;
Any help is appreciated!
Hi,
You don't need to load Geo file into two tables. Instead, you can use ApplyMap to look up city and country names based on OriginId and DestinationId . Below is a sample script using dummy data:
Geo:
LOAD * INLINE [
LocationID, City, Country
23, London, UK
34, New York, US
44, Quebec, Canada
65, Paris, France
];
CityLookup: Mapping LOAD LocationID, City Resident Geo;
CountryLookup: Mapping LOAD LocationID, Country Resident Geo;
Order:
LOAD OrderID,
ApplyMap('CityLookup',OriginId) & '-' & ApplyMap('CityLookup',DestinationId) as [OnD-City],
ApplyMap('CountryLookup',OriginId) & '-' & ApplyMap('CountryLookup',DestinationId) as [OnD-Country];
LOAD * INLINE [
OrderID, OriginId, DestinationId
123,23,44
234,34,65
];
Result of the script
Hope this helps!
BR,
Vu Nguyen
Hi,
You don't need to load Geo file into two tables. Instead, you can use ApplyMap to look up city and country names based on OriginId and DestinationId . Below is a sample script using dummy data:
Geo:
LOAD * INLINE [
LocationID, City, Country
23, London, UK
34, New York, US
44, Quebec, Canada
65, Paris, France
];
CityLookup: Mapping LOAD LocationID, City Resident Geo;
CountryLookup: Mapping LOAD LocationID, Country Resident Geo;
Order:
LOAD OrderID,
ApplyMap('CityLookup',OriginId) & '-' & ApplyMap('CityLookup',DestinationId) as [OnD-City],
ApplyMap('CountryLookup',OriginId) & '-' & ApplyMap('CountryLookup',DestinationId) as [OnD-Country];
LOAD * INLINE [
OrderID, OriginId, DestinationId
123,23,44
234,34,65
];
Result of the script
Hope this helps!
BR,
Vu Nguyen