topic Re: Macro call to oCol.Name fails for expressions in QlikView

Macro call to oCol.Name fails for expressions

vegard_bakke — Tue, 25 Aug 2015 07:42:53 GMT

Hi!

Long story short: I need to write a macro that can look up columns in a straight table / table box, based on the field name.

It all works perfectly well, with one little snag: the macro call oCol.Name fails if the column is an expression, or a calculated dimension. Even if I set a label.

Any ideas to fix or get around this, please?

Function ReadChartColumns()
     Dim oCol, oTable, sChartId

     sChartId = "CH01"


     MsgBox "DBG: Getting columna names from '" & sChartId & "'"
     Set oTable = ActiveDocument.GetSheetObject(sChartId)

     MsgBox "DBG: Reading " & oTable.GetColumnCount & " columns from '" & sChartId & "'"
     For iCol = 0 To oTable.GetColumnCount-1
          'MsgBox "DBG: Getting column: " & iCol
          Set oCol = oTable.GetField(iCol)
          MsgBox "DBG: Got column: " & iCol
          sField = oCol.Name    ' This line fails
          MsgBox "DBG: Got column " & iCol & " field name: " & sField
     Next
End Function

Cheers,

Vegard

Re: Macro call to oCol.Name fails for expressions

petter — Tue, 25 Aug 2015 09:29:51 GMT

All columns are not connected to fields so it is dangerous to assume that the GetField() returns a field object.

Try using this snippet in your function to see what you get:

Set stp = oTable.GetProperties

Set exps = stp.Expressions

Set expr = exps.Item(0)(0).Data.ExpressionData

MsgBox expr.Definition.v

This will return both expressions but also field names .

The object model of QlikView is quite hierarchical and can seem a bit convoluted .....

Re: Macro call to oCol.Name fails for expressions

vegard_bakke — Tue, 25 Aug 2015 11:22:28 GMT

This is definitely the right answer to my question. Unfortunately, I didn't tell you what I need the column name for.

I need to export the some of the columns in a straight table in a peculiar way. My idea was to use the column name as identification/lookup. (I could use column numbers, but it is difficult to read, difficult to maintain, and adding new columns would possibly break the export.)

Therefore I looking for a way to iterate:

'Column name / label / tag' combined with 'Row number', which could give me the 'Cell value'.

I've search through the Qlik's API Guide. But I haven't found a link to the column number, or another way of identifying a cell, from the IArrayOfArrayOfMainExpressionData, IMainExpressionData, IChartDimensionData in the solution above. Or any other classes, for that matter.

However, the API has many members, and I might have missed an easy solution.

Does anyone see a solution to my problem?

Best regards,

Vegard

Re: Macro call to oCol.Name fails for expressions

marcus_sommer — Tue, 25 Aug 2015 11:52:26 GMT

You could read the chart cell for cell within a loop including the header and check them in an if-loop, see here an example from APIGuide.qvw:

set table = ActiveDocument.GetSheetObject( "CH01" )

w = table.GetColumnCount

h = table.GetRowCount

set CellMatrix = table.GetCells2(0,0,w,h)

for RowIter=0 to h-1

for ColIter=0 to w-1

msgbox(CellMatrix(RowIter)(ColIter).Text)

- Marcus

Re: Macro call to oCol.Name fails for expressions

vegard_bakke — Tue, 25 Aug 2015 11:56:18 GMT

That's true, however some columns are for display only, and cannot be exported (breaks the specification).

So I need to somehow specify each column I shall export.

(Preferably without relying on the column number.)

Cheers,

Vegard

Re: Macro call to oCol.Name fails for expressions

marcus_sommer — Tue, 25 Aug 2015 12:11:35 GMT

Maybe other ways are easier, for example to use specialized objects for exports, reporting and printing on a hidden sheet (almost ever I use this approach) or to export objects completely and remove unnecessray content within excel, maybe in a master-file which included such a routine in an OnClose trigger.

- Marcus