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: 
amithmurali
Partner - Creator II
Partner - Creator II

Rename Fields.

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
14 Replies
datanibbler
Champion
Champion

Sure you can rename fields.

Either individually or using a mapping_table - the latter is perhaps more advisable as then you have a sure way of telling which is which and what were the original field_names - even without looking into the script (thinking of others who might not have a license)

HTH

sunny_talwar

Use Pick(Match()) functions like this:

If(Match(location, 'WADI', 'ALN', 'DEIRA', 'DXB', 'MBZ', 'AJN', 'SHJ', 'RAS', 'UML'), Pick(Match(location, 'WADI', 'ALN', 'DEIRA', 'DXB', 'MBZ', 'AJN', 'SHJ', 'RAS', 'UML'), 'AL-WADI', 'AL AIN', 'DXB-DEIRA', 'DXB-IBN', 'MUSSAFAH', 'AJMAN', 'SHARJAH', 'RASALKAIMA', 'UMMAIKOIN'), location) as location

amithmurali
Partner - Creator II
Partner - Creator II
Author

Can you help me with the mapping.

Peter_Cammaert
Partner - Champion III
Partner - Champion III

I'm assumng that you do not want to rename fields, just trnslate values in filed [location], correct?

Create a mapping table from either an INLINE table (there aren't too many values to change) or from an external Excel/Text file with two columns that carry original value/translated value. The latter is more flexible as you can add more values to translate without changing your script. For example, this one can work:

MapTranslation:

MAPPING LOAD * INLINE [

Original, Trnslated

WADI, AL-WADI

ALN, AL AIN

DEIRA, DXB-DEIRA

DXB, DXB-IBN

MBZ, MUSSAFAH

AJN, AJMAN

SHJ, SHARJAH

RAS, RASALKAIMA

UML, UMMAIKOIN

];

Then use applymap() to perform the actual translations when loading the initial values for [location], like in:

LOAD

:

applymap('MapTranslation', [location]) AS [location]

:.

Best,

Peter

kavita25
Partner - Specialist
Partner - Specialist

load * inline [

location,locationname

WADI , AL-WADI

ALN ,AL AIN

DEIRA , DXB-DEIRA

DXB, DXB-IBN

MBZ , MUSSAFAH

AJN ,AJMAN

SHJ , SHARJAH

RAS ,RASALKAIMA

UML , UMMAIKOIN

];

Link the location field with your table location field or else u can use mapping and apply map function to get the full form of location.

Hope it helps you.

Not applicable

Use ApplyMap. Create a doc file like excel with the names alon with the required name and call them in your table using ApplyMap function, to get the required names. You make check applyMap function in Help

ankit777
Specialist
Specialist

Hi

Try this

A:
LOAD * INLINE [
location 
WADI
ALN
DEIRA
DXB
MBZ
AJN
SHJ
RAS
UML
]
;

NoConcatenate
B:
Load location,
pick(match(location,'WADI','ALN','DEIRA','DXB','MBZ','AJN','SHJ','RAS','UML'),'AL-WADI','AL AIN' ,'DXB-DEIRA' ,'DXB-IBN' ,'MUSSAFAH' ,'AJMAN' ,'SHARJAH','RASALKAIMA'
,'UMMAIKOIN' )
as [new loc]
Resident A;
DROP table A; 

maxgro
MVP
MVP

example with your data and mapping

MapLocation:

Mapping load * inline [

from,to

WADI,AL-WADI

ALN,AL AIN

DEIRA,DXB-DEIRA

DXB,DXB-IBN

MBZ,MUSSAFAH

AJN,AJMAN

SHJ,SHARJAH

RAS,RASALKAIMA

UML,UMMAIKOIN

];

Location:

LOAD

  Location as OldLocation,

  ApplyMap('MapLocation', location) as NewLocation

FROM

[https://community.qlik.com/thread/172467] (html, codepage is 1252, embedded labels, table is @1);

jagan
Luminary Alumni
Luminary Alumni

Hi,

Do you want to rename the fields or values in a field?

For fields used below script:

FieldNameMap:

MAPPING LOAD * INLINE [

Original, Trnslated

WADI, AL-WADI

ALN, AL AIN

DEIRA, DXB-DEIRA

DXB, DXB-IBN

MBZ, MUSSAFAH

AJN, AJMAN

SHJ, SHARJAH

RAS, RASALKAIMA

UML, UMMAIKOIN

];

Rename Fields using FieldNameMap;

For changing the fieldvalues:

MapTable:

MAPPING LOAD * INLINE [

Original, Trnslated

WADI, AL-WADI

ALN, AL AIN

DEIRA, DXB-DEIRA

DXB, DXB-IBN

MBZ, MUSSAFAH

AJN, AJMAN

SHJ, SHARJAH

RAS, RASALKAIMA

UML, UMMAIKOIN

];

LOAD

*,

ApplyMap('MapTable', Location, 'N/A') AS MapLocation

FROM DataSource;

Hope this helps you.

Regards,

Jagan.