topic Re: Rename Fields. in QlikView

Rename Fields.

amithmurali — Tue, 14 Jul 2015 10:36:50 GMT

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

Re: Rename Fields.

datanibbler — Tue, 14 Jul 2015 10:39:17 GMT

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.

sunny_talwar — Tue, 14 Jul 2015 10:41:03 GMT

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

Re: Rename Fields.

amithmurali — Tue, 14 Jul 2015 10:41:45 GMT

Can you help me with the mapping.

Re: Rename Fields.

Peter_Cammaert — Tue, 14 Jul 2015 10:44:31 GMT

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

Re: Rename Fields.

kavita25 — Tue, 14 Jul 2015 10:46:26 GMT

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.

Re: Rename Fields.

Tue, 14 Jul 2015 10:47:20 GMT

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

Re: Rename Fields.

ankit777 — Tue, 14 Jul 2015 10:48:05 GMT

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;

Re: Rename Fields.

maxgro — Tue, 14 Jul 2015 10:50:23 GMT

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

Re: Rename Fields.

jagan — Tue, 14 Jul 2015 10:51:23 GMT

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.

Re: Rename Fields.

awhitfield — Tue, 14 Jul 2015 10:53:48 GMT

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;

Re: Rename Fields.

amithmurali — Tue, 14 Jul 2015 11:21:42 GMT

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

Re: Rename Fields.

ankit777 — Tue, 14 Jul 2015 11:24:49 GMT

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

Re: Rename Fields.

Peter_Cammaert — Tue, 14 Jul 2015 11:26:38 GMT

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

Re: Rename Fields.

amithmurali — Tue, 14 Jul 2015 11:30:09 GMT