topic Re: Raw Data from IArrayOfArrayOfRCCell or IArrayOfFieldValue in QlikView

Raw Data from IArrayOfArrayOfRCCell or IArrayOfFieldValue

chrweber — Wed, 25 Nov 2020 16:16:04 GMT

Hi I wish to get a "pointer" or a whole blob of data from the COM Interface to quickly populate an excel range.

Currently i have to copy item-wise.

Is there a way to get at least a whole field at once and "cast" it into a (i.e.) c# array of respective type?

best regards

Re: Raw Data from IArrayOfArrayOfRCCell or IArrayOfFieldValue

marcus_sommer — Tue, 02 Jan 2018 09:06:32 GMT

A field with its values is already an array but you won't be able to transfer it directly to excel as array but you could loop through the field to create a new and interchangeable array. Here an extended example from the APIGuide.qvw:

set val=ActiveDocument.Fields("Month").GetPossibleValues

for i=0 to val.Count-1

msgbox(val.Item(i).Text)

arr(i) = val.Item(i).Text

- Marcus

Re: Raw Data from IArrayOfArrayOfRCCell or IArrayOfFieldValue

chrweber — Wed, 03 Jan 2018 06:24:30 GMT

Hi Marcus, thank you for your reply.

I am well aware of that option, but copying item-wise is very slow.

Fortunately, the com service appears to work using parallel loops, increasing performance by an order of magnitude.

It would have been nice to get a blob of data for copying without any additional overhead/latency.

Re: Raw Data from IArrayOfArrayOfRCCell or IArrayOfFieldValue

marcus_sommer — Wed, 03 Jan 2018 08:04:09 GMT

I would expect that a loop like mentioned above would just need a second even with hundreds/thousands of values - is this already too slow? How many field-values could occur?

What is the aim of your task - maybe there are other ways to reach your aim.

- Marcus

Re: Raw Data from IArrayOfArrayOfRCCell or IArrayOfFieldValue

chrweber — Wed, 03 Jan 2018 08:26:18 GMT

I want to copy tables to excel and be as fast as possible.

Why be anything but?

A parallel copy is maybe 10% faster than copy paste and allows parallel processing, unlike the copy paste option.

Excel allows for simultaneous setting of entire ranges, with minimal latency. So why incur thousands of iterations if a single one will do.

Re: Raw Data from IArrayOfArrayOfRCCell or IArrayOfFieldValue

marcus_sommer — Wed, 03 Jan 2018 08:32:49 GMT

In this case why not just copying the table with something like:

rem ** copy full table to clipboard **

ActiveDocument.GetSheetObject("CH01").CopyTableToClipboard true

and than a simple paste-command within excel.

- Marcus

Re: Raw Data from IArrayOfArrayOfRCCell or IArrayOfFieldValue

chrweber — Wed, 03 Jan 2018 08:38:38 GMT

Because then I could not process multiple tables at once, as the clipboard is single thread only (As far as I know. An alternative would be immensely helpful).

Copying many small tables introduces a lot of latency.

For comparison. The simple sequential copy paste solution takes 28 secs.

parallel copy 24, and additional object parallelism 22 seconds.

These values are crude approxiates and depend on the current load of the system.

Add to that a beefy CPU and you can shave of even more

Re: Raw Data from IArrayOfArrayOfRCCell or IArrayOfFieldValue

marcus_sommer — Wed, 03 Jan 2018 09:36:54 GMT

Ok. I think I understand - you want to transfer a bigger number of tables/objects from QlikView to Excel. And yes it will take some time if you want to do it directly by copying, reading or exporting the objects which indicates that maybe an indirect way is more appropriate.

Indirect meant to store the data from the script-side and/or to export larger rawdata into xls or csv and using them as external data-source within Excel again.

How many tables/objects and records should be transferred to Excel and how long takes the process (maybe a different time-window might be useful, too).

- Marcus

Re: Raw Data from IArrayOfArrayOfRCCell or IArrayOfFieldValue

chrweber — Wed, 03 Jan 2018 09:42:38 GMT

We are talking some dozen small ones(1 row) and a few larger ones (200 rows).

The times are provided in the last post.

What do you mean by the time window?

And as I said, parallel copy of a single table is faster than copy paste (makes sense), which would make a range copy even faster.

Re: Raw Data from IArrayOfArrayOfRCCell or IArrayOfFieldValue

marcus_sommer — Wed, 03 Jan 2018 10:14:26 GMT

I'm not absolutely sure but I doubt that a parallel copying is possible.I think your mentioned approach with the array's of fields/cells meant no copying else a reading of cells over the rows/columns of their objects.

With a different time-window was meant to start this task somewhen overnight and then let it work for a few seconds/minutes.

- Marcus

Re: Raw Data from IArrayOfArrayOfRCCell or IArrayOfFieldValue

chrweber — Wed, 03 Jan 2018 10:17:31 GMT

OK.

No, I am talking seconds and miliseconds for user guided exploration and experimentation purposes and not overnight.

Re: Raw Data from IArrayOfArrayOfRCCell or IArrayOfFieldValue

marcus_sommer — Wed, 03 Jan 2018 10:57:14 GMT

I doubt that it will be possible without an appropriate waiting-time for the user. I think the only possibility to reduce the waiting-time is my suggestion from above with exporting the data (maybe in an external source or in a hidden excel-sheet) and accessing the informations from there within your target cells/tables. It meant generating some kind of an Excel master-template and then just changing/replacing the data-souce for it.

- Marcus