Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
The #1 reason QlikView customers adopt Qlik Sense is a desire for a modern BI experience. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
Specialist II
Specialist II

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

View solution in original post

6 Replies
Highlighted
Specialist II
Specialist II

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

View solution in original post

Highlighted
Luminary
Luminary

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

Highlighted
Not applicable

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

Highlighted
Specialist II
Specialist II

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

Highlighted
Partner
Partner

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

Highlighted
Partner
Partner

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