Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
Region | Country | TOTAL1 | NET |
---|---|---|---|
AS | INDIA | 124 | - |
BANGKOK | 123 | - | |
ASIA | IN | - | 334 |
BK | - | 197 | |
EU | SP | 103 | - |
SPAIN | - | 397 | |
US | NEW JERSEY | 223 | 109 |
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:
Region | Country | TOTAL1 | NET |
---|---|---|---|
AS | INDIA | 124 | 334 |
BANGKOK | 123 | 197 | |
EU | SPAIN | 103 | 397 |
US | NEW JERSEY | 223 | 109 |
Can anyone please help me in this issue.
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;
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;
See a demo here
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?
Well you need to create a mapping table and this would be a onetime exercise that you will need to do manually
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
Thank you Vineeth