Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
fredrik_olsson
Contributor III
Contributor III

Combining fields from two tables in load statement

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:

OrderIdOriginIdDestinationId
1232344
2343465

 

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!

 

1 Solution

Accepted Solutions
vunguyenq89
Creator III
Creator III

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

test.png

Hope this helps!

BR,

Vu Nguyen

View solution in original post

1 Reply
vunguyenq89
Creator III
Creator III

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

test.png

Hope this helps!

BR,

Vu Nguyen