Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

Rename fields in script

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

1 Solution

Accepted Solutions
flipside
Valued Contributor II

Re: Rename fields in script

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

6 Replies
flipside
Valued Contributor II

Re: Rename fields in script

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

matt_crowther
Valued Contributor

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

@QlikviewBI

Not applicable

Re: Rename fields in script

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

flipside
Valued Contributor II

Re: Rename fields in script

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

felcar2013
Contributor II

Re: Rename fields in script

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

felcar2013
Contributor II

Re: Rename fields in script

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

Community Browser