topic Re: Getting random Cell values in QlikView

Getting random Cell values

vlad_komarov — Wed, 05 Dec 2012 17:10:13 GMT

Hello,

I am looking for the way to retrieve a cell data from Straight or Pivot tables. Basically, I need to have an option to click on the table (right click - to avoid selections, for example) and get the cell's value along with Row and Column number.

Copy the cell value to clipboard will not work in my case since I need to know cell's coordinates.

I need to report that specic cell (Column # = A and Row # = B) has a value = C.

Is it possible?

Looking forward to your suggestions.

Best regards,

Vladimir

Re: Getting random Cell values

Gysbert_Wassenaar — Wed, 05 Dec 2012 17:44:48 GMT

I think the only way is to use a macro and that's only possible if you know in advance which row and column you want. There's no click event api for chart objects that can be used.

Sub GetCellValue

RowNo=6

ColNo=2

set obj = ActiveDocument.GetSheetObject( "CH01" )

set cell = obj.GetCell(RowNo,ColNo)

msgbox(cell.Text)

end sub

Re: Getting random Cell values

vlad_komarov — Wed, 05 Dec 2012 18:37:39 GMT

Gysbert,

Unfortunatelly, I will not know which cell customer would like to select.

I have up to 200 columns in one chart (scrollable) and I was looking for the option to "highlight" the specific cell (or range of cells) and copy/paste them into some report (excel file, for example).

But thanks for your reply.

Regards,

Vladimir

Re: Getting random Cell values

vgutkovsky — Wed, 05 Dec 2012 18:43:43 GMT

Vlad, this is probably only possible with an extension object since you can write custom context-menu behavior in JS. Writing an entire extension object just to capture this behavior sounds a bit overkill to me though...In a regular pivot table, when a user clicks on an expression cell, it already automatically highlights (i.e. selects) the dimensions that pertain to that cell. You would then just need to copy the entire table contents, which would give you what you want.

Hope all is well,

Vlad

Re: Getting random Cell values

vlad_komarov — Wed, 05 Dec 2012 19:13:40 GMT

Vlad,

How are you doing?

I do not have much experience implementing extensions, but if remember correctly they could not be executed under IE Plugin. It might be a problem in my case.

Have you seen any examples of this kind of extension objects?

Thank you for your help!

Regards,

Vladimir

Re: Getting random Cell values

vgutkovsky — Thu, 06 Dec 2012 14:08:57 GMT

No, I've never seen this kind of object, it would have to me developed from scratch (like I said, probably not worth the effort).

You can't make the regular pivot table highlighting work for your purposes (when you click an expression value)?

Vlad

Re: Getting random Cell values

marcus_sommer — Thu, 06 Dec 2012 16:39:14 GMT

Read the rows and colums like below and write it in a file. This works also in IE-Plugin.

sub ReadRowColumnValues

dim fso, File, doc, source, iRow, iCol, cell, str

set doc = ActiveDocument

set source = doc.GetSheetObject("CH02")

set fso = CreateObject("Scripting.FileSystemObject")

set File = fso.OpenTextFile(YOUR PATH+FILE.txt, 2, true)

for iRow = 1 to source.GetRowCount - 1

for iCol = 1 to source.GetColumnCount - 1

set cell_1 = source.GetCell(iRow, iCol)

str = "Row " & iRow & " - " & "Column " & iCol & " - " & "Value " & cell.Text

'msgbox str

File.WriteLine str

File.Close

set fso = nothing

set File = nothing

set doc = nothing

set source = nothing

end sub

Marcus

Re: Getting random Cell values

vlad_komarov — Fri, 07 Dec 2012 15:18:34 GMT

Markus,

Thank you for your suggestion, but it will not work in my case. I can achive the same results by using ExportEx method (which will also give me formatting of the original table in Excel file).

I need to "extract" a single cell without entering it Row and Column numbers.

I am trying to develop a mechanism when user can click on single cell or column and "report" values from this cell or column to external file.

Regards,

Vladimir

Re: Getting random Cell values

vlad_komarov — Fri, 07 Dec 2012 15:42:36 GMT

Vlad,

Selection works, but I just need to know (at least) which expression I've clicked on.

Is it possible to detect?

Regards,

Vladimir

Re: Getting random Cell values

vgutkovsky — Fri, 07 Dec 2012 17:04:20 GMT

No, that's not possible as far as I'm aware.

Vlad

Re: Getting random Cell values

marcus_sommer — Fri, 07 Dec 2012 18:23:01 GMT

If your pivot relatively static you could set small buttons in each column which export this single column.

Marcus

Re: Getting random Cell values

vlad_komarov — Fri, 07 Dec 2012 19:35:44 GMT

OK, thanks!

Have a nice Holidays Season and New Year!

Regards,

Vladimir

Re: Getting random Cell values

vlad_komarov — Fri, 07 Dec 2012 19:37:13 GMT

My table(s) are not very "static" - I am giving my client an option to show/hide specific columns, but your suggestions looks like the only solution so far.

Thanks!

Regards,

Vladimir

Re: Getting random Cell values

Wed, 27 Feb 2013 17:29:00 GMT

If I would like to activate that cell to make that the Chart Navigate forward more associative information, what should i do?

Sub GetCellValue

RowNo=6

ColNo=2

set obj = ActiveDocument.GetSheetObject( "CH01" )

set cell = obj.GetCell(RowNo,ColNo)

msgbox(cell.Text)

//Selects the cell to navigate forward in current data chart

cell.select(): //how to select that cell????

end sub

Re: Getting random Cell values

marcus_sommer — Wed, 27 Feb 2013 20:29:11 GMT

The topic is similar - perhaps it is helpful.

http://community.qlik.com/thread/73166?start=0&tstart=0

- Marcus

Re: Getting random Cell values

Thu, 28 Feb 2013 01:25:59 GMT

Expressions are not selectable. However, in a pivot table, you can make the expression into a dimension. This is how it works:

The existing dimensions remain the same.
Create a new field (e.g., expression_field) with the values 1 to the number of expressions needed.
Use a pick function for the expression that will choose the actual expression based on the expression_field dimensions (.e.g., pick(expression_field, expression1, expression2, expression3).
There should only be one expression.

The "expressions" are now selectable.

In practice, the pick function becomes slow with many expressions. Depending on the complexity of the expression, this method maybe unusable before you reach 200 expressions.

Re: Getting random Cell values

Wed, 24 Sep 2014 23:26:33 GMT

Gysbert.

How could i use macro a pivot table.

eg.

Expression = GetCellValue(11,1)

i sent as parameters the row and column and the function returns me the value of the cell.

1) How to define the function

2) As i invoke the function