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

    Rename fields in script

    Göran Hofstedt

      Hi

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

           

          flipside

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

                else

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

                endif

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

                NEXT i

                 

                 

              • Re: Rename fields in script
                Matthew Crowther

                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

                @QlikviewBI

                  • 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

                        data2:

                        LOAD * INLINE [

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

                        1,1,1,1,1,1,1];

                         

                        store data2 into data2.txt (txt);

                         

                        FldMap:

                        MAPPING LOAD @1 as FldNameOld,

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

                        FROM

                        [data2.txt]

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

                        Rotate(left)

                        ));

                         

                        RENAME FIELDS USING FldMap;

                         

                         

                        flipside