topic Multi variable analysis Write to Fields not Excel in QlikView

Multi variable analysis Write to Fields not Excel

Sat, 20 Jun 2009 02:17:57 GMT

I have created a Multi Variable Anaylis VBS and am currently reading and writing to Excel. I want to be do this solely in Qlikview by loading the data from the system and then creating fields after the calculations have been completed. Does anyone know the syntax that I need to use to create the results into a field and load the data from a table and not the Spread sheet. VB script attached.

The Load

Set objXL = CreateObject("Excel.Application")
Set objWB = objXL.WorkBooks.Open("\\Bailey2009.xls")
Set objWS = objWB.WorkSheets("Analysis")
objXL.Visible = True

For I1 = 1 To NumLevels1
For I2 = 1 To NumLevels2
For I3 = 1 To NumLevels3
Row = (I1 - 1) * NumLevels2 * NumLevels3 + (I2 - 1) * NumLevels3 + I3 + 1
Exposures(I1, I2, I3) = objXL.Cells(Row, 4)
CellRel(I1, I2, I3) = objXL.Cells(Row, 7)
Next
Next
Next

Here I am writing out to the Excel SS but I want to create fields.

For i = 1 To NumLevels1
objXL.Cells(6 + i - 1, 13) = Result1(i)
Next

For i = 1 To NumLevels2
objXL.Cells(14 + i - 1, 13) = Result2(i)
Next

For i = 1 To NumLevels3
objXL.Cells(22 + i - 1, 13) = Result3(i)
Next

Multi variable analysis Write to Fields not Excel

rwunderlich — Sat, 20 Jun 2009 06:39:59 GMT

I'm not familar with an API that lets you create rows in a table (InputFields and the QV9 dynamic updates may be exceptions).

Why not just continie to write to the XLS file and have the load script read that file?

-Rob

Multi variable analysis Write to Fields not Excel

Wed, 24 Jun 2009 21:54:19 GMT

It works as is now. What we want to do in the future is do a more data intensive analysis where the intial data could be larger than 60,000 rows. Excel has a 60,000 limitation. WE could bring the data in with multiple SS's but I am trying to make this as simple as possible for the user. What I would like to do is keep it all in qlikview so we dont have to worry about the limitation. Thanks, Troy

Multi variable analysis Write to Fields not Excel

Wed, 24 Jun 2009 22:11:25 GMT

As Rob mentioned, Dynamic Data Updates in QlikView 9 may be the answer. You can use SQL-like commands to update data into your documents.

If you download the new, version 9 APIGuide, there is an example. Go to Automation Examples and select the Document Class and DynamicUpdateCommand in the Member box.

I haven't had a chance to test out this new feature, but it seems to be a pretty cool addition.

Multi variable analysis Write to Fields not Excel

rwunderlich — Wed, 24 Jun 2009 22:14:16 GMT

I don't think it's possible with V8 to add significant amounts of new data to a document with macro code. That would mess with the optimized data model built by the load. May be something in V9.

Only workaround I can think of is to write the data to a CSV file and reload from there using Document.DoReload or ReloadEx. Of course, the user will need permission to reload the document. I've seen this technique used before. For example, http://community.qlik.com/media/p/63030.aspx

-Rob

Multi variable analysis Write to Fields not Excel

Tue, 30 Jun 2009 03:46:34 GMT

Thanks for the help, I've got a similar issue. Where would one find the new version 9 APIGuide?

Thanks!

Paul

Multi variable analysis Write to Fields not Excel

Wed, 01 Jul 2009 20:48:19 GMT

http://www.qlik.com/download/

Select version 9 and English and it's the top item.