topic Re: how to store table read by macro in a variable?? in QlikView

how to store table read by macro in a variable??

Wed, 25 Nov 2020 16:16:04 GMT

i have a table rows and columns read by macro code -

Sub ReadStraightTable

Set Table = ActiveDocument.GetSheetObject( "CH05" )

For RowIter = 1 to table.GetRowCount-1

For ColIter = 2 to table.GetColumnCount-1

set cell = table.GetCell(RowIter,ColIter)

MsgBox(cell.Text)

End Sub

Now i have to store it in a a variable?? any clue or help needed.

Re: how to store table read by macro in a variable??

Gysbert_Wassenaar — Thu, 21 Jul 2016 15:51:03 GMT

You can't store a table in a variable. Well, not a Qlikview variable. I suppose you could store it in a vbscript variable. But more importantly... what are you trying to achieve anyway?

Re: how to store table read by macro in a variable??

Thu, 21 Jul 2016 16:21:49 GMT

hello gysbert,

i want to calculate xirr , but i want the values should come from table in qlikview(which is dynamic) and i want to print xirr value in a text box object, in text box object it should take input as the variables or by table object id.

Re: how to store table read by macro in a variable??

Thu, 21 Jul 2016 16:33:00 GMT

Im trying to calculate xirr , but the values should come from straight table(which is dynamic i.e. its user dependent selection based) that is in qlikview and then i want a text box in which the xirr will be displayed. the text box input parameter should be variables. and if i store it in vb script variable, then how should i use it as parameter in text box for any calculation??

Re: how to store table read by macro in a variable??

Gysbert_Wassenaar — Fri, 22 Jul 2016 07:39:24 GMT

but the values should come from straight table(which is dynamic i.e. its user dependent selection based

The XIRR function of Qlikview is also dependent on user selections.

Re: how to store table read by macro in a variable??

Fri, 22 Jul 2016 13:28:55 GMT

OK but i want to store the output of cells into vb variable so that i can use it to calculate in table box by just passing varaible names , lik for eg. xirr(var1,var2). now in this it wil calculate only if var1 is holding all dates and var2 is holding all amounts.

Re: how to store table read by macro in a variable??

Gysbert_Wassenaar — Fri, 22 Jul 2016 14:19:24 GMT

Why? What's wrong with calculating it directly using an expression?

Re: how to store table read by macro in a variable??

Fri, 22 Jul 2016 14:29:13 GMT

have a look at attachment.

i want to calculate the xirr based on xirr_date and xirr_payment from table ch05 and the table changes accroding to the date in the left hand side. now i am getting to read values from the table ch05 now i want to store the amount in variable vValue1 and date in variable vValue 2 and then pass it to text box as xirr(vValue1,vValue2). can you help me out with this.

thanks.

Re: how to store table read by macro in a variable??

Fri, 22 Jul 2016 14:41:50 GMT

hello Gysbert

plz have a look at this attachment, now im able to read column 2 and column 3 that is date and amount, now i want to store the first column values in var 1 and second amount column in var 2 . so i can pass it to text box for xirr calc, as xirr(val1, val2). if you could help me with this.

thanks

Re: how to store table read by macro in a variable??

Gysbert_Wassenaar — Fri, 22 Jul 2016 16:31:25 GMT

Sorry, that just makes totally no sense at all to me. I have no idea what on earth you're trying to do.

Re: how to store table read by macro in a variable??

Fri, 22 Jul 2016 18:09:20 GMT

based on dates selection my table ch05 gets updated , ok

now im not able to get XIRR(Payments,ActivityDate) , when i make any change in date (list box - which is in left top, see my attachment). its very challenging question that im trying to solve.

Re: how to store table read by macro in a variable??

Gysbert_Wassenaar — Fri, 22 Jul 2016 18:23:07 GMT

That Date field in the list box on the left is totally unconnected to the rest of the model. So using it in CH05 is totally just generating completely bogus numbers. It's all nonsense afaict.

Re: how to store table read by macro in a variable??

Fri, 22 Jul 2016 18:27:40 GMT

if you dont want to help dats fine but do not tell like its nonsense, ok if u dont know dats fine too. but u have no right to say anything u want. ok that date list box is connected to CH05 table, ok based on selection of date , the ch05 table changes and i want to find xirr of the dynamic date and payment values.k

Re: how to store table read by macro in a variable??

Fri, 22 Jul 2016 18:28:42 GMT

and the data is not real, its a dummy , just to test functionality, nobody uploads any original data of the firm or watever it is k

Re: how to store table read by macro in a variable??

Gysbert_Wassenaar — Sat, 23 Jul 2016 11:51:31 GMT

nobody uploads any original data of the firm or watever it is k

You'd be suprised. But that doesn't matter. Dummy data is fine as long as it's representative and logical.

Re: how to store table read by macro in a variable??

Gysbert_Wassenaar — Sat, 23 Jul 2016 11:54:35 GMT

The example you posted just doesn't make sense. You simply get a cartesian product when you use that unconnected Date field. Expression results will repeat for each Date value. Why do you need that?

Perhaps you should simple export your straight table directly to excel and calculate whatever you think you need in excel.

Re: how to store table read by macro in a variable??

Mon, 25 Jul 2016 12:59:34 GMT

hello gysbert, the example i posted is not correct.

Can you help me or give a suggestion for how to read table (which is having two coulmns, one is date and another one is amount, and it is dyanmic, means the values keeps on changing based on user selection) and calculate XIRR from the two columns of table. if i update the table , then new xirr should be calculated and displayed.

thanks

Re: how to store table read by macro in a variable??

Gysbert_Wassenaar — Mon, 25 Jul 2016 17:01:30 GMT

Can you post a file (excel is file) with example data and the results you expect based on that data?