7 Replies Latest reply: Mar 4, 2013 9:52 AM by cristie629 RSS

    Calling a sheet object in a variable

      I asked this question in another thread, but it deserves it's own thread, I think.


      Is there a way to point to an object ID, and then a column in that object, in a variable?


      Something like:


      = (ObjectID(ColumnNumber)) ?

        • Re: Calling a sheet object in a variable



          You can try with getcell function..


          I am not yet tried it.


          This below example I got it from API guide:


          set table = ActiveDocument.GetSheetObject( "CH01" )

          for RowIter = 0 to table.GetRowCount-1

              for ColIter =0 to table.GetColumnCount-1

                  set cell = table.GetCell(RowIter,ColIter)






          • Re: Calling a sheet object in a variable

            By writing macro , Its working..



            Sub StraightTableCell

            set table = ActiveDocument.GetSheetObject( "CH01" )

                    set cell = table.GetCell(1,1)


            End Sub

            • Re: Calling a sheet object in a variable

              Here's a picture of the table I'm referring to.


              See the "RC02 Min Percentage" cell? That does a calculation to determine the lowest percentage value of all the values in the table.


              I need to call to that, in a bar chart. I have about 10 of these straight tables, and I need to show each table's lowest percentage value inside of the bar chart.


              I'm hoping there's a way to set a variable to that table's cell (which is called column(10)), and then call the variable inside of the bar chart.



                • Re: Calling a sheet object in a variable

                  I guess my question is, how does the variable know which table I'm talking about?


                  In the "RC02 Min Percentage" column, the expression just says "if ((Column(3)<=Column(5)), Column(3), Column(5))"... It's a simple if statement to pull the min value down.


                  If I put that in the variable, it won't know which of my 10 tables I'm talking about. They all have columns.


                  Is there a better, more distinct way to do the min, that the variable would know which fields I'm speaking of, in which tables?

                    • Re: Calling a sheet object in a variable
                      Gysbert Wassenaar

                      It doesn't. If you want to reuse an expression don't use column references. Use variables that are chart (-layout) independant. For example:


                      vExpr1: sum(Value1)

                      vExpr2: sum(Value2)

                      vExpr3: rangemax( $(vExpr1), $(vExpr2))


                      Column3: $(vExpr1)

                      Column5: $(vExpr2)

                      RC02 Min Percentage: $(vExpr3)


                      An expression in some other chart: $(vExpr3). If that chart uses the same dimensions it will return the same value.



                      The $ is to force evaluation of the variable. It's called dollar expansion. You can see how that works by trying it out in a text box =vVariance versus =$(vVariance).

                        • Re: Calling a sheet object in a variable

                          okay I think I see what you're saying - could you humor me, and tell me how I would set it up, in my example?


                          I'm pretty confused when it comes to variables and how to set them up, and I'm only about 2 weeks new to Qlikview. lol.


                          Would I make a variable for column 3 and 5, and put the same expression I now have in those columns inside of the variable?


                          Does the $ make the variable know it's a value I'm returning, I think?


                          Then, I can call to the variable in my bar chart, and return the value in the variable?


                          Thanks a ton for this, by the way.