topic Re: Replacing script field names using mapping table in QlikView

Replacing script field names using mapping table

felcar2013 — Mon, 25 Feb 2013 12:56:06 GMT

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:

Re: Replacing script field names using mapping table

Mon, 25 Feb 2013 14:22:48 GMT

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.

Re: Replacing script field names using mapping table

jeffmartins — Mon, 25 Feb 2013 15:07:05 GMT

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

Re: Replacing script field names using mapping table

Mon, 25 Feb 2013 15:14:39 GMT

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

Re: Replacing script field names using mapping table

felcar2013 — Mon, 25 Feb 2013 15:40:09 GMT

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

Re: Replacing script field names using mapping table

felcar2013 — Mon, 25 Feb 2013 16:23:09 GMT

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;

Re: Replacing script field names using mapping table

jeffmartins — Mon, 25 Feb 2013 16:30:05 GMT

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

Re: Replacing script field names using mapping table

felcar2013 — Mon, 25 Feb 2013 17:09:06 GMT

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

Re: Replacing script field names using mapping table

jeffmartins — Mon, 25 Feb 2013 17:27:40 GMT

Hi felcar2013,

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

See the attached file.

Regards

Re: Replacing script field names using mapping table

felcar2013 — Mon, 25 Feb 2013 18:19:48 GMT

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

Re: Replacing script field names using mapping table

jeffmartins — Mon, 25 Feb 2013 19:04:54 GMT

Hi felcar2013,

You need to put the code "Rename fields using FieldMap" at the end of the script.

The fields are only renamed after the code "Rename fields using FieldMap".

Regards

Re: Replacing script field names using mapping table

felcar2013 — Tue, 26 Feb 2013 10:40:23 GMT

thanks

but it did not work. I need to a kind of "find/replace" function that goes through all tables and fieldnames in the script and replace the old fields with the new fields. This shall happen for 200 different field names.

I can just not execute the script without getting a lot of field name erros, since it loads from qvd files, which already have the new names, that were changed in the data source (sql database).

thanks anyway

Re: Replacing script field names using mapping table

devans_1 — Tue, 26 Feb 2013 10:48:30 GMT

Can you set a variable using SET and use this variable ? That way you only need to change the field once at the top of the script ?

SET field1 = "contact_periodID";

LOAD $(field1)

etc.

Re: Replacing script field names using mapping table

felcar2013 — Tue, 26 Feb 2013 18:04:13 GMT

the mapping solution with rename fields works when i load inline, not when i need to load from external data sources. Besides, it seems that the fields are changed in-memory, not in the script, what is what i need.

David, i will see your option, even though i already changed everything manually 🙂 took some time, but i wanted to automate it and need to continue trying, since this will happen again,

best

felipe