QlikView - VbScript - C#

[Anonymous]

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?

[Anonymous]

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?

[marcus_sommer]

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

[Anonymous]

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

[marcus_sommer]

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

function VBGetValue(Product)

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

end function

- Marcus

[Anonymous]

Thank you Marcus!

I think this is the right way.

[Anonymous]

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

[marcus_sommer]

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

[Anonymous]

Good morning Marcus. This seems a great idea.

How could I do that?

Could you please post an example of your suggestion?

[marcus_sommer]

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