Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
hugmarcel
Specialist
Specialist

Convert TableBox to StraightTable with Macro Solution

'Copy theTableBox as new straight table, use theExcludeField of theTableBox as dimension or expression, if not ""
'Call CopyTableBox2NewStraightTable("TB01", true, false, "<expressionfield>")
'Call CopyTableBox2NewStraightTable("TB02", false, true, "<dimensionfield>")
sub CopyTableBox2NewStraightTable(theTableBox, asDimensions, asExpressions, theExcludeField)
  'Create new chart:
  set chart = ActiveDocument.Activesheet.CreateStraightTable
 
  'Read theTableBox items, add to new chart
  set tb = ActiveDocument.GetSheetObject(theTableBox)
  set tbp = tb.GetProperties
  set cols = tbp.Layout.ColLayouts
 
  for i = 0 to tb.GetColumnCount-1
    set fld = tb.GetField(i)
 vColumnField = fld.Name
    vColumnLabel = fld.Name
    If (Len(cols.Item(i).Label.v) > 0) Then
  
      vColumnLabel = cols.Item(i).Label.v
    End If
 'Set items either as dimensions or as expressions
 If (asDimensions) Then
   'Add dimension
   If (vColumnField <> theExcludeField) Then
     chart.AddDimension vColumnField
  
     'Add label to dimension if <> dimension
     If (vColumnLabel <> vColumnField) Then
       set cp = chart.GetProperties
          set dims = cp.Dimensions
          dims(i).Title.v = vColumnLabel
          chart.SetProperties cp
       set cp = Nothing
          set dims = Nothing
        End If
      Else
     'Add dummy expression, finally
     chart.AddExpression "=Only([" & vColumnField & "])"
    
     'Set expression name and total mode
     set cp = chart.GetProperties
     j = cp.Expressions.Count - 1
     cp.Expressions.Item(j).Item(0).Data.ExpressionVisual.Label.v = vColumnLabel
     cp.Expressions.Item(j).Item(0).Data.ExpressionData.UsePartialSum = false
        chart.SetProperties cp    
      End If     
 ElseIf (asExpressions) Then
   'Set dimension to chart
      If (vColumnField = theExcludeField) Then
        chart.AddDimension theExcludeField
     set cp = chart.GetProperties
        set dims = cp.Dimensions
        dims(0).Title.v = vColumnLabel
        chart.SetProperties cp
     set cp = Nothing
        set dims = Nothing
   Else
     'Set new expression
     chart.AddExpression "=Only([" & vColumnField & "])"
  
     'Set expression name and total mode
     set cp = chart.GetProperties
     j = cp.Expressions.Count - 1
     cp.Expressions.Item(j).Item(0).Data.ExpressionVisual.Label.v = vColumnLabel
     cp.Expressions.Item(j).Item(0).Data.ExpressionData.UsePartialSum = false
        chart.SetProperties cp
      End If
 End If
  Next
 
  'Apply table box format to chart format
  'Do it manually once new straigt table exists.
 
  'Finish
  set fld  = Nothing
  set cols = Nothing
  set tbp  = Nothing
  set tb   = Nothing
 
  set chart = Nothing
end sub
Labels (1)
1 Reply
hugmarcel
Specialist
Specialist
Author

I have copied some more items from the table box, such as window title, show condition, etc. However, I do not manage to copy the exact same Position (top, left, width, height) of the table box to the new straigt table. Maybe somebody knows how to do so:

  'Copy tablebox title
  set cp = chart.GetProperties
  cp.GraphLayout.WindowTitle.v = tbp.Layout.Frame.Name.v
  
  'Copy the calculation condition
  cp.EvaluationCondition.v = tbp.EvaluationCondition.v
 
  'Copy the show condition
  cp.GraphLayout.Frame.Show.Always = false
  cp.GraphLayout.Frame.Show.Expression.v = tbp.Layout.Frame.Show.Expression.v
 
  'Copy the calculation errors messages
  for i = 0 to cp.GraphLayout.ErrorMessageOverrides.Count-1
    cp.GraphLayout.ErrorMessageOverrides.Item(i).v = tbp.ErrorMessageOverrides(i).v
  next 
 
  'Copy position: does not work, new chart does not take exact position of tablebox
  'mh dim dummy(0) 'all parts
  'mh set fr = chart.GetFrameDef
  'mh set pos = fr.Rect
  'mh pos.Top    = tbp.Layout.Frame.Rect.Top
  'mh pos.Left   = tbp.Layout.Frame.Rect.Left
  'mh pos.Width  = tbp.Layout.Frame.Rect.Width
  'mh pos.Height = tbp.Layout.Frame.Rect.Height
  'mh set pos = fr.MinimizedRect
  'mh pos.Top    = tbp.Layout.Frame.Rect.Top
  'mh pos.Left   = tbp.Layout.Frame.Rect.Left
  'mh pos.Width  = tbp.Layout.Frame.Rect.Width
  'mh pos.Height = tbp.Layout.Frame.Rect.Height  
  'mh chart.SetFrame fr, true, dummy
 
  'Copy position: does not work, new chart does not take exact position of tablebox
  pos = chart.GetRect
  pos.Top    = tb.GetRect.Top
  pos.Left   = tb.GetRect.Left
  pos.Width  = tb.GetRect.Width
  pos.Height = tb.GetRect.Height
  chart.SetRect pos
     
  'Apply table box format to chart format
  'Do it manually once new straigt table exists.
  chart.SetProperties cp