topic QlikView - VbScript - C# in QlikView

QlikView - VbScript - C#

Anonymous — Wed, 18 Jan 2017 11:16:55 GMT

Good morning,

it's my first message in this board because I always found everything I needed.

There's something I didn't find and I'm trying to understand if it's impossible to do.

So..let me explain what I'd like.

I've a qvw document script like that:

LOAD Customer,

[Sales Order ID],

ShipDate,

Product,

VBGetValue() AS FromDotNet,

Sales,

Quantity

FROM

[Sales Orders.xls]

(biff, embedded labels, table is [Sales Orders$]);

VBGetValue is a function defined in the Edit Module window:

function VBGetValue()

dim myObj

Set myObj = CreateObject("MyNamespace.MyClass")

VBGetValue = myObj.myMethod(ActiveDocument)

end function

I'm able to communicate with my registered C# DLL and to know the right type of COM ActiveDocument object: it's a Doc.

So..i tried to cast in the C# side this parameter with Doc class within Interop.QlikView.dll in this way:

public string myMethod(object a)

{

try

{

var doc = (Doc) a;

return doc.Name;

}

catch (Exception ex)

{

return ex.Message;

}

And this is the Exception Message caught:

Error HRESULT E_FAIL has been returned from a call to a COM component. MyClass -2147467259 at QlikView.Doc.get_Name()

What's wrong?

Re: QlikView - VbScript - C#

marcus_sommer — Wed, 18 Jan 2017 11:41:02 GMT

Maybe this is helpful: Calling a DLL function in my QV script ...‌

- Marcus

Re: QlikView - VbScript - C#

Anonymous — Wed, 18 Jan 2017 13:13:13 GMT

Unfortunately not!

Re: QlikView - VbScript - C#

petter — Wed, 18 Jan 2017 14:17:52 GMT

Your main impediment here and actually a roadblock is that when a load script is running the QlikView object model is not available. It seems to me that your are trying to access the currently running load script's object model - right? QlikView Desktop can be in one of two phases. Running load script and executing the application that use the data model that the load script produced. These phases aren't overlapping. And only the second phase instantiate and make available a COM Object Model.

At no time during load script execution is there any contact with the QlikView Object Model. The only thing you can get in contact with is the standard VBScipt (or JScript) engine of the Windows OS. This VBScript only has access to the standard features as documented by Microsoft. That is why the VBGetValue() actually works. But your C# code - from what you show us can't make any calls back to an object that is not available or does not exist.

Only afterwards when the load script has finished and made ready the in-memory data model for consumption of a QlikView session will the QV Object Model be available. The connection point from a COM-perspective is that you are first connected to either QlikView Desktop or QlikView Server and then you can through COM either get the ActiveDocument or Open an existing QVW-file/document or create a new document. So Macros/Modules invoked through the UI of either QV Desktop, IE plug-in (Internet Explorer only), QV OCX or even a regular web-browser (with limitations of functionality for this specific option).

So in your C# you can't get in contact with an object model of the application that is running the load script. A hack that would be rather odd would be that you could be able to independently connect directly with QlikView in a separate instance of QlikView (maybe) and then open an old/previous instance of a document. That would seem very convoluted and without knowing your use-case I would advice strongly against anything like that...

Re: QlikView - VbScript - C#

marcus_sommer — Wed, 18 Jan 2017 15:58:56 GMT

Which kind of information respectively values should be really returned from VBGetValue() - maybe there are other ways to get them - probably not the documentname() or is it?

- Marcus

Re: QlikView - VbScript - C#

Anonymous — Thu, 19 Jan 2017 08:37:40 GMT

Yes Marcus. You are right!

What I need is to pass arrays of data from resident tables and return back them.

Is it possible to pass fields values?

Re: QlikView - VbScript - C#

Anonymous — Thu, 19 Jan 2017 08:38:03 GMT

Thank you very much!

Re: QlikView - VbScript - C#

marcus_sommer — Thu, 19 Jan 2017 13:47:50 GMT

Do you mean with "resident tables" already in these qvw loaded data? If yes then they are in general accessable. Could you give a more practically example to them - how and where loaded and where should they be access again?

- Marcus

Re: QlikView - VbScript - C#

Anonymous — Thu, 19 Jan 2017 14:08:17 GMT

Sure Marcus,

my intentions are like the image below.

I would like to pass a field and to return a modified "Product" value through VBScript (and maybe within the VBScript be able to call a c# dll passing the field value)

I hope it's clear.

Thank you in advance!

Re: QlikView - VbScript - C#

marcus_sommer — Thu, 19 Jan 2017 14:13:36 GMT

In which way should "Product" be modified? Please give an example for a typical input- and output-value.

- Marcus

Re: QlikView - VbScript - C#

Anonymous — Thu, 19 Jan 2017 14:19:49 GMT

Mmm....really I don't need to modify it, I need to analyze this product string following a set of rules and then to create a log through log4net using C#.

Is it possible?

Re: QlikView - VbScript - C#

marcus_sommer — Thu, 19 Jan 2017 14:41:29 GMT

If you mean with analyzing the string with a set of rules something like regular expressions then you should take a look here: http://www.qlikfix.com/2010/10/18/regular-expressions-in-the-load-script/‌ but maybe there are even easier solutions possible and the efforts to rebuild the dll-logic may not so big.

And of course you could store those results within a log-file in a csv- or xml-fileformat which might quite similar to the output from log4net.

- Marcus

Re: QlikView - VbScript - C#

Anonymous — Thu, 19 Jan 2017 14:54:18 GMT

Yes Marcus sure, but how can I pass the field value to the MyVBScript function?

Re: QlikView - VbScript - C#

marcus_sommer — Thu, 19 Jan 2017 14:59:49 GMT

This would be quite similar to your first-statement above:

function VBGetValue(Product)

VBGetValue = mid(Product, 3,2) 'or whatever ...

end function

- Marcus

Re: QlikView - VbScript - C#

Anonymous — Thu, 19 Jan 2017 15:07:50 GMT

Thank you Marcus!

I think this is the right way.

Re: QlikView - VbScript - C#

Anonymous — Thu, 19 Jan 2017 15:27:25 GMT

Marcus is it possible to have access to a mapping table within the MyVBScriptFunction and then to pass it to C#?

Re: QlikView - VbScript - C#

marcus_sommer — Thu, 19 Jan 2017 15:55:55 GMT

Not really and not as a mapping-table itself but you could aggregate each field within an array of fieldvalues and those fieldarrays are further concatenated within an array of fields and transfering this array to your vbs-function you could rebuild the structure there - just with split() on your set delimiter and then looping through the values.

But I could imagine that there are other and easier ways for it and maybe you won't need the vbs and could do it just with inbuilt qlikview features. Could you please give an example to which Product string leads to which returned result respectively which output within the log-file?

- Marcus

Re: QlikView - VbScript - C#

Anonymous — Fri, 20 Jan 2017 11:00:09 GMT

Good morning Marcus. This seems a great idea.

How could I do that?

Could you please post an example of your suggestion?

Re: QlikView - VbScript - C#

marcus_sommer — Sat, 21 Jan 2017 10:32:48 GMT

Here an example what was meant:

TableFieldArray:

load concat(Year, ',') & '|' & concat(recno(), ',') as TableFieldArray;

load 2010 + recno() as Year AutoGenerate 7;

let vTableFieldArray = peek('TableFieldArray', 0, 'TableFieldArray');

and then you could transfer these variable to vbs as function-parameter. But like above mentioned it could be that you could do all your adjustment, analyzing and/or output directly with qlik-features - and should this be possible you should rather not mix up different tools.

- Marcus