Rename field in only one table

[sspe]

Hi,

I'm looking for a smart way to load a resident table but prefixing all the fields with a string.

As an example, I have loaded a Customer dimension, but now I'd like to create a SellToCustomer and a BillToCustomer dimension where all the fieldnames are prefixed with SellTo and BillTo.

Of course I can just write my load statement and specify all fields with an alias, but I was looking to a way to avoid this.

I have also looked at the RENAME FIELD statement, but that changes the name in all tables and not only in e.g. the BillTocustomer table. At least, I can't see that I can tell it to only change it in a specific table.

Using the QUALIFY feature could also be an option, but I don't want the "." in the field name, and I can't see that I can avoid that with the QUALIFY statement.

Anyone who have an idea to solve this?

Regards

Steen

[sspe]

Hi Adam,

Since you are using RENAME FIELD, I think it will be the same problem as I already have.

In my case I have e.g loaded a Customer dimension.

Now I'd like to load the same fields but into a BIllToCustomer table and rename the fields to BillToCustomer_....

If I just use RENAME FIELD, I'll also get the fields in the existing Customer table renamed, which ot course isn't what I want.

It seems like I'm either missing the option to do a RENAME FIELD on only a specific table or if using the QUALIFY statement, then be able to define the "delimiter" (e.g. to "_" og "" instead of the default ".").

Maybe this could be an idea to Qlik to add these features :-).

Regards

Steen

[adamdavi3s]

Hi Steen,

Well you could run my loop for one specific table, but it won't help if one of those fields is joined as you can't rename to an existing field. Oh hang on, try this (might need a tweak):

qualify *;

BillToCustomer:

LOAD *

Resident Customer;

Unqualify *;

For f = 1 to NoOfFields('BillToCustomer')

    let vFieldOld = FieldName($(f),'BillToCustomer');

    let vFieldNew = Replace('$(vFieldOld)','BillToCustomer.',''BillToCustomer_);

    RENAME FIELD $(vFieldOld) to $(vFieldNew);

Next f; 

[sspe]

Thanks, but I'm still not sure if this will be an easy solution to my issue. As I see the code, I'd also need to do a lot of writing everytime I need to "duplicate" a new table. In thas case, I think it's just as easy (and easy understandable ) just to write a new LOAD statement with an alias (AS) for each field.

Regards

Steen

[Michiel_QV_Fan]

Use a preceding load to rename the field and then drop the original field from the table.

drop field A from x;

[adamdavi3s]

Hi Steen,

You shouldn't need to re-write the code each time at all.

However using the AS statements is probably the easiest solution, however will a full set of requirements I bet it would be easy to script.

If you named your key fields say starting with 'keyxxxxxxx' then you could simply exclude these from the rename loop.

[sspe]

Hi Michiel,

I'm not sure I understand how a preceding load will make it easier in this case?

No matter if I do a preceding or resident load, I'll still have to alias all the fields manually?

Regards

Steen

[Michiel_QV_Fan]

If you only want a field in the BillToCustomer table be changed, this could do the trick.

BillToCust_table:

Load

     BillToCust     as     [BTC Bill to customer],

     *;

Load

     BillToCust,

     ......

From ......;

drop field BillToCust from BillToCust_table;

All other fields in your script that are named BillToCust will keep that name.

This is your requirement that I understood from your question.

[sspe]

Not exactly. I was looking for a more "automated" way to prefix all fields in a RESIDENT load so I don't have to type the whole LOAD statement.

Apparently there are no easy way to do this. I think the closest I get, is to use QUALIFY but then I'll either have to live with the "." as delimiter or write a piece of code that can get rid of the "." in all the columnnames in the table.

/Steen

[Michiel_QV_Fan]

mapsubstring function in combination with rename fields to replace the Billtocust. with Billtocust<space>?

[#11]

I found this thread trying to solve a different problem and this syntax really helped me Michiel van de Goor - thank you.

I wanted to rename a specific field in a specific table, without always knowing what the other fields would be.

Results:

LOAD

     Date as [Res Date],

     *

FROM Results.qv(qvd);

Drop Field Date from Results;

Easy peasy - no need to list all the other fields. Thought I would share here in case anyone else is browsing and has the same problem as me.