Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

amithmurali
Contributor 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
Esteemed Contributor

Re: Rename Fields.

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

Re: Rename Fields.

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
Contributor II

Re: Rename Fields.

Can you help me with the mapping.

Re: Rename Fields.

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
Valued Contributor

Re: Rename Fields.

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

Re: Rename Fields.

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
Valued Contributor

Re: Rename Fields.

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; 

MVP
MVP

Re: Rename Fields.

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);

MVP & Luminary
MVP & Luminary

Re: Rename Fields.

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.