6 Replies Latest reply: Feb 25, 2013 1:35 PM by Felipe Carrera RSS

    Rename fields in script

    Göran Hofstedt


      Is there any more..elegance way to rename field in the script then "as" ?

      I load it as a crosstable and have to rename it as shown in pict.


      load year.JPG

        • Re: Rename fields in script
          Dave Riley

          You can loop through the fields of a table with this ...


          For f = 1 to NoOfFields('data')

              let vFieldOld = FieldName($(f),'data');

              let vFieldNew = Replace('$(vFieldOld)','bef','');

              RENAME FIELD $(vFieldOld) to $(vFieldNew);

          Next f;   


          ... where data is the name of your table, and 'bef' is the string to be replaced with ''.



            • Re: Rename fields in script
              Felipe Carrera

              what if the string is not fix, but variable like:


                   Load * Inline [

                   FieldID, Field_1, Field_2

                   136, contact_CustomerSince_group_Name, member_MemberSince_Group_Name
                  137, ABC_AdvertisingCountry, ABC_Advertising_Country
                  138, contact_LastPurchase_group, member_LastPurchase_Group
                   139, contact_Discount_Percent_12mon_group_Discount, member_Markdowns_Percent_12mon_Group_Name
                  140, contact_RegistrationChannel, member_RegistrationChannel
                  141, contact_LastStoreName, member_LastStore_Name
                 142, transaction_StoreNumber, transaction_Store_Number
                  143, transaction_StoreSalesChannel, transaction_Store_SalesChannel
                  144, transaction_StoreCountry, transaction_Store_Country



              field_1 is the field to be replaced and field_2 shall stay

              • Re: Rename fields in script
                Felipe Carrera

                I used a script like this, but it shows always an error message


                FOR i = 1 to NoOfFields(FieldMap)

                LET vFieldOld = FieldName($(i), 'FieldMap');

                if $(vFieldOld) = left('$(vFieldOld)',8) then

                LET vFieldNew = Replace('$(vFieldOld)','contact', 'member');


                LET vFieldNew = Replace('$(vFieldOld)', mid($(vFieldOld),13),'member');


                RENAME Field $(vFieldOld) to $(vFieldNew);

                NEXT i



              • Re: Rename fields in script

                You can rename fields using a Mapping Load, just search the help file for 'Mapping' for a walk through.


                Probably not much quicker or easier in the long run though.


                All the best,


                Matt - Visual Analytics Ltd

                Qlikview Design Blog: http://QVDesign.wordpress.com


                  • Re: Rename fields in script
                    Göran Hofstedt

                    as you say map is not a quiker alternative, but the loop function seems to be. Will try that, thanks!

                      • Re: Rename fields in script
                        Dave Riley

                        Possibly the easiest way to create a mapping table if you want to avoid looping is by storing the table to a text file then reading it back in with a transformation step as follows ...


                        //Method 2 - via mapping table


                        LOAD * INLINE [

                        bef2009, bef2010, bef2011, bef2012, bef2013, bef2014, bef2015



                        store data2 into data2.txt (txt);



                        MAPPING LOAD @1 as FldNameOld,

                             Replace(@1,'bef','') as FldNameNew



                        (txt, utf8, no labels, delimiter is ',', no quotes, filters(




                        RENAME FIELDS USING FldMap;