Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Join us March 10th, 7 Ways Modern Analytics Can Help You Take Smarter Action. REGISTER 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

View solution in original post