Add label to straight table column based on dimension name

[swearengen]

I'm trying to update labels based on the field name. I've tried the code below but it doesn't work because no label has yet been added ie it would work for updating existing label names but doesn't work when no label name is present. How do I extract the dimension (or column or field or whatever you want to call it) name ie what should go where the bolded text is?

I've tried the API but I may as well be looking into a hedge.

Thanks,

Stephen

Sub RenameFields

set table=ActiveDocument.GetSheetObject("TB20")

set tbp=table.GetProperties

set cols = tbp.Layout.ColLayouts

for i = 0 to cols.Count-1

If (cols.Item(i).Label.v = "joint_acc_no_join") Then cols.Item(i).Label.v = "Account Number"
If (cols.Item(i).Label.v = "joint_account_tally") Then cols.Item(i).Label.v = "account_tally"
.
.
.
next

table.SetProperties tbp

End Sub

[marcus_sommer]

Why not naming the fields within the script like they should be used within the UI? Depending on the requirements there may always one or two occasions in which a different UI label is needed but those could be done manually. Using macros to apply such renaming-jobs for a lot of objects is IMO not expedient.

[swearengen]

Thanks for the commentary. Do you have any idea what the answer is? 

[marcus_sommer]

You mean in regard to your macro? I'm not sure but I think you need to query the included dimensions for it because the dimension-name and the label are two different properties and if no label is set the dimension-name is taken.

So you may loop between two properties and comparing the results. Unfortunately I couldn't say which property it is. You may find an appropriate example or at least suitable hints within the APIGuide.qvw easier if you use:

info(Classmember)

within a listbox (it's essential part from the text-box which showed possible examples if any available) and then applying various search-strings and hovering with the mouse over the results of the list-box.

[swearengen]

Appreciate the pointers Marcus. I'll give it a go. I'm aware it's not best practice and it could all be done manually or by a simple reload with aliases but it's just piqued my curiosity at this point. I had also thought to export the table structure and build a simple non-looping script based on that but the export doesn't seem to include the index for the ColLayout array which seems like a bit of an oversight. Anyways thanks again.

[marcus_sommer]

In regard to the renaming it could be also done per mapping. Maybe within the last report-layer after a binary load by remaining the origin data-base names through the entire ETL chain.

Rename field | QlikView Help

[swearengen]

Yep that makes sense although I don't want to rename the fields. I'm merging some dashboards that are very similar so I've been aliasing the fields on load (with a joint_,  sole_ , etc appended to each name).
In my final product I'll have several tables with different field names (eg joint_acc_no in one, sole_acc_no in another) where I want the display column header in each of the tables to read 'Account Number'.
Hence the need for labels as I can't rename different fields to the same name. Not an intractable problem, and certainly not how I would have done things with a clear run at it but you inherit what you inherit.  

[marcus_sommer]

In many scenarios it's more suitable not to join or to associate such tables else to concatenate (union in sql) them into a single fact-table by harmonizing the field-names and data-structures and adding an extra SOURCE field which could be used as dimension and/or selection and/or expression-condition to differentiate between the data.

[swearengen]

Thanks Marcus.

Does anyone else have a solution to the specific question I asked?