topic Re: Read resident table in VBScript Macro in QlikView

Read resident table in VBScript Macro

Fri, 26 Jul 2013 20:53:17 GMT

I currently have a table which controls the display of certain measures in a macro. Right now I am reading from a database via ADO to capture this table in my macro, but I was wondering if I could more easily read from the table line by line in a recordset directly from QV after loading the table in the script. This would be much more efficient and failsafe.

Here is my macro below:

Public Sub FetchRollups

selectedLevel = ActiveDocument.fields("Levels").GetSelectedValues.Item(0).Text

Dim conn

Dim rs

'Instantiate connection and recordset objects

Set conn = CreateObject("ADODB.Connection")

Set rs = CreateObject("ADODB.Recordset")

'Open the connection and capture the P&L Rollup table

conn.ConnectionString = "DSN=CXAPPSVR03"

conn.Open

Set rs = conn.Execute("SELECT * FROM TEST.DBO.C_X_PLDEF WHERE LEVEL <= " & selectedLevel)

'Grab the relevant chart and associated properties

set chart = ActiveDocument.GetSheetObject("CH02")

prevcount = chart.GetProperties.Expressions.Count

'Add all expressions from recordset

Do While not rs.EOF

'Add expression and capture its ID

x = chart.AddExpression(rs("ExpressionValue").Value)

'Set expression properties such as label

Set prop = chart.GetProperties

prop.Expressions.Item(x).Item(0).Data.ExpressionVisual.Label.v = String(rs("Level")*3, " ") & rs("ExpressionText").Value

chart.SetProperties prop

'Continue Loop

rs.MoveNext

Loop

'Delete old pdimensions

for y=prevcount-1 to 0 Step -1

chart.RemoveExpression y

'Cleanup chart

'chart.SetPixWidth 0,200

conn.Close

'chart.SetProperties p

End Sub

Re: Read resident table in VBScript Macro

marcus_sommer — Sat, 27 Jul 2013 18:44:21 GMT

You could load your expression-table directly in the load-script and put each one afterwards in a variable and use these variables as expression. A macro wont needed.

Connect ...

ExpressionsTable:

Select * From ExpressionFrom Database;

for i = 1 to FieldValueCount('Expressions')

set expr1 = peek('Expressions', $(i) - 1, 'ExpressionsTable');

....

- Marcus

Re: Read resident table in VBScript Macro

Sat, 27 Jul 2013 19:05:25 GMT

But then how do I get these expressions into a pivot table dynamically? There can be a different number of expressions.

Re: Read resident table in VBScript Macro

marcus_sommer — Sat, 27 Jul 2013 19:23:51 GMT

The expressions themself are static but the content is dynamically:

expr1 = $(expr1)

expr2 = $(expr2)

....

The same could be done with the label and comment from the expression and also by the conditions which then control different number of expressions.

- Marcus

Re: Read resident table in VBScript Macro

Sat, 27 Jul 2013 20:51:11 GMT

I'm confused. How would I control the number of expressions conditionally displayed?

Re: Read resident table in VBScript Macro

marcus_sommer — Sun, 28 Jul 2013 08:34:00 GMT

Conditions for dimensions/expressions are only available since QV 11. Your expressions-record could beside the label, expression and comment also have a field for the condition - a simply true/false or also an expression on any fields.

If you use a version older as QV 11 you can't control the number of expressions on this way, then you must put the expressions with a macro. But the load the expressions from the database into a table/fields/variables could be done and then per macro with an OnOpen-Trigger put in the chart you want.

- Marcus

Re: Read resident table in VBScript Macro

Mon, 29 Jul 2013 21:01:53 GMT

All - I don't think the above solution is going to work. I simply have too many conditional expressions, and the performance is terrible. My macro is really only slow because I am reading a Table Box. That read process is incredibly slow based on me commenting in and our parts of the script.

There has to be an easier way.

Re: Read resident table in VBScript Macro

marcus_sommer — Tue, 30 Jul 2013 07:35:03 GMT

It isn't really clear what you mean. The dynamically befilling from the expressions (with which solution) is too slow or the calculations from these objects by open/select?

- Marcus

Re: Read resident table in VBScript Macro

jarno_loubser — Wed, 11 Dec 2013 10:32:38 GMT

I have the same requirement - reading a resident table in macro code. Reading through a front-end object is just too slow.

Anyone?

Re: Read resident table in VBScript Macro

marcus_sommer — Wed, 11 Dec 2013 11:26:49 GMT

The fastest way to read a table is always directly within a load-statement. If you couldn't work with dynamically expressions per variables, you could also create a tablebox with these data within the gui and read these object in a loop per macro. I didn't understand what should be too slow with such a solutions ...

- Marcus

Re: Read resident table in VBScript Macro

jarno_loubser — Wed, 11 Dec 2013 11:31:19 GMT

please see http://community.qlik.com/thread/101563

Re: Read resident table in VBScript Macro

marcus_sommer — Wed, 11 Dec 2013 12:24:12 GMT

This isn't the same requirement - you want change values and not set properties.

- Marcus