topic Rename fields in script in QlikView

Rename fields in script

Thu, 18 Oct 2012 13:05:26 GMT

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.

Re: Rename fields in script

flipside — Thu, 18 Oct 2012 13:45:04 GMT

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

matt_crowther — Thu, 18 Oct 2012 13:45:53 GMT

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

Thu, 18 Oct 2012 14:01:44 GMT

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

flipside — Thu, 18 Oct 2012 14:26:26 GMT

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

Re: Rename fields in script

felcar2013 — Mon, 25 Feb 2013 16:41:48 GMT

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

felcar2013 — Mon, 25 Feb 2013 18:35:56 GMT

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