Replacing script field names using mapping table

[felcar2013]

HI

i have a big script and unfortunately we changed field names in the data base which affects my qvds and other parts of the script

I wanted to know, if it is possible to replace the Fieldname_1 with the fieldname_2 at once in all the script tabs with a do-while-loop statement. I was using the Find / Replace, but it is too manual and it is getting caothic. Besides, you see that i need not only to replace the first word "contact" with "member" but also, in some cases, the second part of the string, like in the

"contact_DummyCustomer" with "member_NonMember". Is there a "replace" function in the script that evaluates the current field, compare it and then based on the condition "replaces" it accordingly?

mapping table:

[#2]

Hi,

The best way to organize this issue is dividing phased extraction data in your scripts.

The first step is only read information from the database files and then generate the QVD.

The second step is to read this information from the first step and realize their visions and tables facts. After generating a file QVD with the result of this information.

The third and final step is the generation of indicators.

If there is a change of field names in your database, just the first step would be affected.

Step 1:

Tab_A:

LOAD contact_PeriodID

        , contact_DummyCustomer

         ...

SQL Select contact_PeriodID,

                   contact_DummyCustomer

                   ...

         FROM data_base;

STORE * from Tab_A into Tab_a.qvd;

After change in the database:

Tab_A

LOAD member_PeriodID AS contact_PeriodID

        , member_NonMember AS contact_DummyCustomer

        ...

SQL Select member_PeriodID,

                  member_NonMember

                  ...

        FROM data_base;

STORE * from Tab_A into Tab_a.qvd;

Following these steps greatly decreases the work to be done.

[jeffmartins]

Hi felcar2013,

You can replace the fields names using a mapping table.

Example:

FieldMap:

Mapping SQL SELECT oldnames, newnames FROM datadictionary;

Rename fields using FieldMap;

See the attached file. Hope this helps you.

Regards

[#4]

I did not know this command syntax. It will be very useful!

[felcar2013]

Thanks Eduardo,

what i need to change are the fieldnames in the script, this is, all "contact" strings must be replaced by "member". Your solution is appropriate  if I still need to keep the "contact" string in my application. I have what you say, in the second step the loading fields start with "member" and i save the fieldnames like that as qvd. (this happens in one qv document). in a second one, i load data from the qvds and odbc connection directly, and the script does not work since the fields were changed.

thanks

felipe

[felcar2013]

thanks

i got something like this:

LOAD

* INLINE [

    Field_ID, Field_1, Field_2

    1, contact_PeriodID, member_PeriodID

    2, contact_ID, member_ID

    3, contact_CustomerNumber, member_CustomerNumber

    4, contact_DummyCustomer, member_NonMember

    5, contact_Gender, member_Gender

    6, contact_Age, member_Age

    7, contact_Age_group, member_Age_Group

    8, contact_PostalCode, member_PostCode

    9, contact_AdvertisingCountry, member_Advertising_Country

];

and wanted to loop them, but i need to replace the old field column with the new one, which is a variable string.

why do you use Table1 and Table2? i cannot follow the logic behind it?

Table1:

LOAD * INLINE [

    A, B, C

    1, 2, 4

];



Table2:

LOAD * INLINE [

    C, D, E, F

    4, 2, 4, 0

];



Rename fields using FieldMap;

[jeffmartins]

Hi felcar2013,

Table1 and Table2 are the tables you want the field names replaced.

All field names are replaced at once. You don't need to loop them.

Regards

[felcar2013]

thanks Jeff

it means, if i have 40 tables, do i need to put them all?

in your example, what are the numbers? i do not see the new fields in the final tables

[jeffmartins]

Hi felcar2013,

The numbers are values of the fields I wanted to change the field names.

See the attached file.

Regards

[felcar2013]

thanks, now i understand, but it means, if i have more than 40 tables, in different script tabs, where should i locate the code? My script is not recognizing the fields when i load them from qvd files. These files have the right names, so i do not need to change anything there, but in the qv document where the script is located