topic Re: Renaming Fields to names that already exists in QlikView

Renaming Fields to names that already exists

rustyfishbones — Wed, 29 Mar 2017 15:03:31 GMT

Hi,

I have the following Sub Routine, I loop through all the tables and fields and find the prefix and rename the field without the prefix.

However, when I call the routine, I get an error message when trying to rename a field that is used on another table (If the field is unique across all tables it works perfectly!!)

take the below example using ctrl + Q + Q to generate random data, notice I have Num in 3 tables, however when I try to remove the prefix of . by renaming it, I get an error message as the field is named in 2 other tables

before you ask

I add the prefix of . to create a data island of dimensions that can be used for adhoc reporting (still having other issues with that :-))

Re: Renaming Fields to names that already exists

rwunderlich — Wed, 29 Mar 2017 15:51:48 GMT

It's a restriction that the RENAME statement cannot rename to an existing name. The workaround is to recreate the field using the AS clause.

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Re: Renaming Fields to names that already exists

marcus_sommer — Wed, 29 Mar 2017 15:53:21 GMT

Yes, your approach couldn't work because the renaming won't match/merge the different symbol-tables behind the fields - I think you will need a different method to create your loosen tables.

- Marcus

Re: Renaming Fields to names that already exists

rustyfishbones — Fri, 31 Mar 2017 08:12:25 GMT

Thanks Both.

Rob, Have you created a Sub Routine that loops over the tables and fields and creates a Data Island for Adhoc Dimensions.

I am trying to create a Data Island Table if the fieldname begins with .

I have it working for the _Dimension, but I am struggling to get an output similar to below. Not really sure how to Dynamically create the ID and ParentID

AdhocDimensions_tmp:

LOAD * INLINE [

ID, ParentID, _Dimension

A, , Users

1A, A, OperativeName

B, B, Calendar

1B, B, Year

2B, B, Month

3B, B, Week

4B, B, Weekday

5B, B, Shift

6B, B, Date];

I can create another thread if it's something you could shed some light on.

Thanks

Alan

Re: Renaming Fields to names that already exists

rwunderlich — Mon, 03 Apr 2017 07:29:31 GMT

Alan,

When creating a table for adhoc Dimensions, I generally just load the fieldname as _Dimension. I don't both with any id field. Can you show how (the chart expression/conditional) you are using the adhoc dim?

-Rob

Re: Renaming Fields to names that already exists

rustyfishbones — Mon, 03 Apr 2017 15:59:43 GMT

Hi Rob,

I have the List Box as Tree View, based on the table I generate, but I want to dynamically create the below table

I then display the TreeView_Dimension as a List box.

but I want the code to create the above table. so if I have fields called

[.Customer_Name],

[.Customer_Type] (the . means I want to include it as an adhoc dimension)

I would like to create them in the AdhocDimensions_Tmp.

So it would look like below

C, , Customer

1C, C, Customer_Name

2C, C, Customer_Type

I hope that explains it.