topic Re: Using field names as labels in QlikView

Using field names as labels

Mon, 30 Mar 2015 15:24:15 GMT

Hi - I have field names that are too long and they have the format of db-name.table-name.column-name. By default, Listbox uses the entire field names as Labels. I would like to exclude the prefix and just use table-name.column-name in my case. Is there a way to manipulate the field names to remove the prefix? Thanks for any help!

Re: Using field names as labels

stigchel — Mon, 30 Mar 2015 15:54:21 GMT

You can rename the fields at the end of your load script using a mapping. yhis will aslo not change your data model. From the help

Using the Rename Fields Statement

This is a very good method if you want to rename all or some fields at the end of the script run. The best way is to use a mapping table with the old and new field names and use this as follows.

Example:

FieldNameMap;

Mapping Load OldFieldName, NewFieldName From FieldNames ;

Rename Fields using FieldNameMap;

You can store the mapping table in your database or in an Excel sheet so that it is easier to maintain.

Re: Using field names as labels

rajkumarb — Mon, 30 Mar 2015 16:00:09 GMT

Hi Greg

Renaming fields

There are two main reasons for a developer to rename a field:

• To ensure that two tables are associated through the correct fields when

originally these two tables did not share a field with the same name, but

a link does in fact exist between them.

• To prevent unwanted associations between tables when they share a field

with the same name but that field does not actually represent the link

between them.

To rename a field, we can simply use the as keyword in the Load script to assign an

alias to the original field name. For example, take the following Load script, in which

we hypothetically load the Airport descriptions:

[Table_Name]:

LOAD

Code as [New_Field_Name],

Description as [New_Field_Name]

FROM

QlikView 11 for Developers

HTH

Re: Using field names as labels

Mon, 30 Mar 2015 16:02:35 GMT

Thanks, but can this be done in the UI instead of the load script? We have developers working on scripts and the designers working on the UI. I have the designer role.

Re: Using field names as labels

rajkumarb — Mon, 30 Mar 2015 16:06:54 GMT

HI Greg

In List box

General-un select - use as Title

IN Caption Select show Caption

Entre your New Field Name there

Re: Using field names as labels

Mon, 30 Mar 2015 16:18:49 GMT

Thank you Raj, but my question is,

I have field names that are too long and they have the format of db-name.table-name.column-name. By default, Listbox uses the entire field names as Labels. I would like to exclude the prefix and just use table-name.column-name in my case. Is there a way to manipulate the field names to remove the prefix?

Re: Using field names as labels

rajkumarb — Mon, 30 Mar 2015 16:27:11 GMT

HI Greg

There is another option (in script or in chart)

Right(field, len(field) - index(field, '['))

Re: Using field names as labels

stigchel — Mon, 30 Mar 2015 16:36:49 GMT

No, I would ask the developers who work on the script. Otherwise you will have to manually set each listbox title by typing in the text where it says use field name

Re: Using field names as labels

Tue, 31 Mar 2015 06:07:10 GMT

Thanks. I'm actually looking for something like that, but instead of spelling out the field name, is there a variable or system variable that represents the name of the field. For example, if the field name is 'abc.xyz' and I just want 'xyz', does a variable vfieldname exist that allows me to do something like this -

Right(vfieldname, len(vfieldname) - index(vfieldname,'.'))?

Re: Using field names as labels

Peter_Cammaert — Tue, 31 Mar 2015 07:02:55 GMT

You mean a variable that contains the name of the current object? Not as a standard feature. But I could be mistaken, beause there really is an undocumented QV function that gets you the current sheet.

I you are persistent in this, you can look into writing a macro that gets the field name of the listbox. (Don't forget to enable the use of macro functions in QV expressions in hidden options) However, I'm not sure how to get a reference to the current object...

As a side note: you're clearly running into one of the reasons why - somewhere along the different stages of the dataflow - it's a good idea to start thinking in QlikView concepts instead of dragging along those database names. Usually this level of abstraction is introduced in the QDS-phase. For you, the big advantage of this approach would be that at the same same this layer can target the end-user and name all tables and fields in a consise and consistent way. Which is not always the case in source data. Imagine that in the future you want to present self-service BI to your end-users. Should they learn the structure of your RDBMS by heart?

Best,

Peter

Re: Using field names as labels

stigchel — Tue, 31 Mar 2015 09:16:49 GMT

I did some searching and testing and I think you can use GetObjectField(0) so in your example of 'abc.xyz' where you want 'xyz' :

=SubField(GetObjectField(0),'.',2)

Re: Using field names as labels

Peter_Cammaert — Tue, 31 Mar 2015 09:28:34 GMT

Not so undocumented after all. Good find!

See QV Help -> System Functions

Re: Using field names as labels

Thu, 02 Apr 2015 07:17:24 GMT

You brought up a very good point. At the moment I'm only working with database table and field names. I'm new to Qlikview, and I'm just experimenting different options. In order to manage tens of databases, hundreds of tables and thousands of fields in my production environment, I think eventually I need to be able to map the friendly field names in the load script. Thanks for the help and advice.

Re: Using field names as labels

Thu, 02 Apr 2015 07:19:01 GMT

You're a genius! Thanks. This is what I was asking for at the beginning.

Re: Using field names as labels

stigchel — Thu, 02 Apr 2015 08:58:22 GMT

You're welcome Greg.

If this has answered your question can you please close this thread by marking one of the answers correct? It will help others find a (correct) answer to similar problems and lets other contributing members know that this requires no more attention.

Thanks,

Piet Hein

Re: Using field names as labels

buzzy996 — Thu, 02 Apr 2015 09:09:11 GMT

subfield() is the right option to remove some content form source field.

exp: SubField(SubField(`Authoriser comments`,'</p>',1),'<p>',2) as AuthorisercommentsNew,

Re: Using field names as labels

Peter_Cammaert — Thu, 02 Apr 2015 21:17:30 GMT

Hi Greg, can you please undo your Correct Answer-assignment and mark the original post by Piet Hein at Re: Using field names as labels.as correct?

Have a look at your OP. The reply marked as Correct now appears inside the frame to form a quick Q&A overview.

Unfortunately the answer that is currently shown - although polite and very relevant - has nothing to do with your original question. A question asking you to mark a correct answer is never the correct answer itself. As the original author, you can always change this assignment.

Thanks,

Peter

Re: Using field names as labels

Thu, 02 Apr 2015 21:29:25 GMT

Ok Peter. I did it. Hope I got it right this time.

Re: Using field names as labels

Peter_Cammaert — Thu, 02 Apr 2015 21:34:41 GMT

Perfect !