Rename Fields.

[amithmurali]

Hi guys.

I have a problem in creating a data model. names are different in Fact tables. is there anyway we can rename it. Only 9 fields are there to rename.

If the table contains the name like this.

location
WADI
ALN
DEIRA
DXB
MBZ
AJN
SHJ
RAS
UML

I need to rename it like this

WADI => AL-WADI

ALN => AL AIN

DEIRA => DXB-DEIRA

DXB => DXB-IBN

MBZ => MUSSAFAH

AJN => AJMAN

SHJ => SHARJAH

RAS => RASALKAIMA

UML => UMMAIKOIN

And the table should look like this,

location
AL-WADI
AL AIN
DXB-DEIRA
DXB-IBN
MUSSAFAH
AJMAN
SHARJAH
RASALKAIMA
UMMAIKOIN

[awhitfield]

Hi Amrith,

You can use a MAPPING LOAD, to replace the abbr with the full values, as simple INLINE version as follow:

CountryMap:
Mapping Load * Inline
[
Country, NewCountry
WADI,AL-WADI
ALN,AL AIN
DEIRA,DXB-DEIRA
DXB,DXB-IBN
MBZ,MUSSAFAH
AJN,AJMAN
SHJ,SHARJAH
RAS,RASALKAIMA
UML,UMMAIKOIN
];



Data:
Load * Inline
[
Country
WADI
ALN
DEIRA
DXB
MBZ
AJN
SHJ
RAS
UML
];

NewCountry:

Load

ApplyMap('CountryMap', Country) as NewCountry
Resident Data;

Drop Table Data;

[amithmurali]

Can you tell me how to write the script if i have stored the new names in an excel sheet (Mapname.xlsx). and how to write the script with that. please have a look of my script.

[Mappingtable]:

LOAD Location,

     NewNames

FROM

[..\Data\ExternalData\Mapname.xlsx]

(ooxml, embedded labels, table is Sheet1);

LOAD Location,

KPI_NAME,

     DEPT_ID,

     DEPT_NAME,

     ACH_NOS,

     ACH_VAL,

     TAR_NOS,

     TAR_VALUE

FROM

[..\Data\IntermediateQvd\KPIdata.qvd]

(qvd);

[ankit777]

This should work

LOAD Location,

KPI_NAME,

     DEPT_ID,

     DEPT_NAME,

     ACH_NOS,

     ACH_VAL,

     TAR_NOS,

     TAR_VALUE

FROM

[..\Data\IntermediateQvd\KPIdata.qvd]

(qvd);

left join

LOAD Location,

     NewNames

FROM

[..\Data\ExternalData\Mapname.xlsx]

(ooxml, embedded labels, table is Sheet1);

[Peter_Cammaert]

Like this:

[Mappingtable]:

MAPPING LOAD Location, NewNames

FROM [..\Data\ExternalData\Mapname.xlsx] (ooxml, embedded labels, table is Sheet1);

LOAD applymap('Mappingtable', Location) AS Location,

     KPI_NAME,

     DEPT_ID,

     DEPT_NAME,

     ACH_NOS,

     ACH_VAL,

     TAR_NOS,

     TAR_VALUE

FROM [..\Data\IntermediateQvd\KPIdata.qvd] (qvd);

applymap with 2 parameters will return the original field value if it cannot find that value in the translation table. Your translation table needs only supply the values to translate, not the ones that shouldn't be touched.

Also keep in mind that Mapping Tables disappear at the end of your script.

Peter