topic Re: QV & Powershell in QlikView

QV & Powershell

Fri, 12 Jun 2015 15:11:00 GMT

Hi all,

i'm looking for help using Qlikview and powershell.

the aim is to get the last value of my Qv and insert it in a parameter table in oracle DB.

I did this :

sqlplus user/user@DWH_WRITE

update test set DATE_ID=sysdate;

commit;

the script execute wel the first ligne, but not the others.

regads

Re: QV & Powershell

petter — Fri, 12 Jun 2015 15:35:43 GMT

Your QlikView-application will it be run as:

1) QlikView Desktop - File Open in Server?

2) Internet Explorer via AccessPoint - IE-plugin?

3) Full Browser (Ajax) via AccessPoint?

It is possible to write a VBScript Macro that sends the command or some text via Named Pipes to

a PowerShell script that is listening to a Named Pipe and then it could do exactly what you want.

I have done exactly that written the VBScript-macro and sent information to a PowerShell Named-pipe server.

Re: QV & Powershell

petter — Fri, 12 Jun 2015 15:56:49 GMT

Sample code that has been tested in QlikView Desktop:

VBSCRIPT:

Sub TestPipe

Dim msg_sent, msg_resp

pipeName = "ForWrite"

' Remember to turn on the security settings: Allow System and Allow System Access

Set fso = CreateObject("Scripting.FileSystemObject")

Set pipe = fso.OpenTextFile("\\.\pipe\" & PipeName, 2, True)

msg = "Shouting 'Hello World!' through a Named Pipe"

pipe.Write ( msg )

'msg_resp = pipe.ReadLine()

'MsgBox resp

pipe.Close

End Sub

POWERSHELL:

$npipeServer = new-object System.IO.Pipes.NamedPipeServerStream('ForWrite', [System.IO.Pipes.PipeDirection]::In)

try {

$npipeServer.WaitForConnection()

$i = (new-object System.IO.StreamReader($npipeServer)).ReadToEnd()

}

finally {

$npipeServer.Dispose()

}

Re: QV & Powershell

petter — Fri, 12 Jun 2015 15:59:35 GMT

As an alternative you could push this info via a simple HTTP GET by having av special constructed URL very easily too....

Re: QV & Powershell

Mon, 15 Jun 2015 08:33:09 GMT

the goal is to update a table with a value that comes from a QlikView when reloading.

Re: QV & Powershell

petter — Mon, 15 Jun 2015 08:42:49 GMT

You can still use VBScript from your load script by calling a VBScript Function. But you could also use EXECUTE statement in your load script to invoke PowerShell directly.

Re: QV & Powershell

Mon, 15 Jun 2015 08:47:53 GMT

invoke PowerShell directly, that's what i want to do. but, i don't know how ;(

Re: QV & Powershell

petter — Mon, 15 Jun 2015 09:00:53 GMT

This is a simple one-lines that doesn't do much at all but illustrates the concept:

EXECUTE C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe Write-Host Hello World! | Pause;

or even

EXECUTE powershell.exe Write-Host Hello World! | Pause;

It is important that the Account you are using to run the load script is allowed to execute PowerShell scripts of course.