Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
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
as you say map is not a quiker alternative, but the loop function seems to be. Will try that, thanks!
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
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
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