Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Data Joining in QlikView

Hi All

         I have two tables with some same field names but some data differs in them,for example,Region in one table contains Asia,Europe,US but in other table it was AS,EU,US and Country in one table contains IN,BK etc but in other table it was India,Bangkok etc..The problem here is that when I am creating a pivot table fields are getting joined and forming synthetic keys,but measures column is differing in data as shown below:

RegionCountryTOTAL1NET
ASINDIA124-
BANGKOK123-
ASIAIN-334
BK-197
EUSP103-
SPAIN-397
USNEW JERSEY223109

where TOTAL1 is amount according to Region in one table and NET is amount according to Region in other table.

What should I do to get data in two columns as it was in last row.

My data finally should look like:

RegionCountryTOTAL1NET
ASINDIA124334
BANGKOK123

197

EUSPAIN103397
USNEW JERSEY223109

Can anyone please help me in this issue.

1 Solution

Accepted Solutions
vinieme12
Champion III
Champion III

Use ApplyMap() to modify the values and harmonise the dataset for consistency

For Example

MapRegion:

Mapping LOAD * INLINE [

Region,RegionShortName

ASIA,AS

EUROPE,EU

];

MapCountry:

Mapping LOAD * INLINE [

CountryShortCode,CountryName

IN,INDIA

BL,BANGKOK

];

YOURTABLE:

LOAD *

Applymap('MapRegion',Region) as Region,

Applymap('MapCountry',Country) as Country

FROM YOURDATASOURCE;



Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.

View solution in original post

6 Replies
vinieme12
Champion III
Champion III

Use ApplyMap() to modify the values and harmonise the dataset for consistency

For Example

MapRegion:

Mapping LOAD * INLINE [

Region,RegionShortName

ASIA,AS

EUROPE,EU

];

MapCountry:

Mapping LOAD * INLINE [

CountryShortCode,CountryName

IN,INDIA

BL,BANGKOK

];

YOURTABLE:

LOAD *

Applymap('MapRegion',Region) as Region,

Applymap('MapCountry',Country) as Country

FROM YOURDATASOURCE;



Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
vinieme12
Champion III
Champion III

See a demo here

http://qlikshare.com/194/

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Anonymous
Not applicable
Author

Hi Vineeth

Thank you for your reply.It was working fine for Region,but I am having many countries which makes it difficult for joining them,is there any other way to solve this?

vinieme12
Champion III
Champion III

Well you need to create  a mapping table and this would be a onetime exercise that you will need to do manually

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
vinieme12
Champion III
Champion III

If you have ISO shortcodes for the county in your data, then i suggest googling for an entire list of countries with ISO shortcodes and you could map the regions yourself

so you can use the same data for mapping/renaming

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Anonymous
Not applicable
Author

Thank you Vineeth