14 Replies Latest reply: Jul 14, 2015 7:30 AM by Amith Murali RSS

    Rename Fields.

    Amith Murali

      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.
          Friedrich Hofmann

          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.
              Amith Murali

              Can you help me with the mapping.

                • Re: Rename Fields.
                  Kavita Vishwakarma

                  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.
                    jagan mohan rao appala

                    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.
                  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

                  • Re: Rename Fields.
                    Peter Cammaert

                    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.
                        Amith Murali

                        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.
                            Ankit Mishra

                            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

                              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.
                            haimanta Barkakati

                            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.
                              Ankit Mishra

                              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; 

                              • Re: Rename Fields.
                                Massimo Grossi

                                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.
                                  Andrew Whitfield

                                  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;