topic Re: Issue with Qlikview Publisher in QlikView

Issue with Qlikview Publisher

Wed, 12 Feb 2014 05:30:31 GMT

Hi Guys,

I have a really weird issue with Qlikview Publisher.

I have an application that generates qvds every hour(Vendor data).

I have another qvw application that runs post the generation of my qvds,so that my application has latest data.This one runs after every successful reload of the QVD generator application(indirectly every hour as well.) as defined on qlikview publisher.

Now my application has two table boxes containing some columns with some data ,and this needs to be pushed in two seperate tables in Oracle Database. For this I have used a macro,and via a VB script I have pushed data. I am aware of the technical issues by Qlikview Publisher and data push,but, When i schedule this on the server,data from the first table box gets populated successfully every hour,but the data from the second table box does not get populated in its respective table in Oracle.However, when I manually run the macro,both tables get updated successfully in Oracle.

Has anyone faced this issue yet?

All the same,what might be the problem,can anyone give me a headsup?

Best,

Rochelle

Re: Issue with Qlikview Publisher

Miguel_Angel_Baeyens — Wed, 12 Feb 2014 05:34:45 GMT

Hi,

The issue is likely that the macro is not being run at all. When you call it and where? Is it in the script or as a trigger? The proper way to do it is to create a new supporting task in the QMC depending on the others to have succedded that launches a CMD or a CSCRIPT command that runs Windows-side the VBS file withe the macro.

Miguel

Re: Issue with Qlikview Publisher

Wed, 12 Feb 2014 06:31:34 GMT

Hi Miguel,

Many thanks for your reply.But,The macro is running.I have done the same steps mentioned by you. I can say that is running because,for my first tablebox,say there is a vendor created now,I see it in my oracle table after an hour,which is perfect as per my macro.

Let me run you through the steps I ve followed:

1)On QMC,there is a tab called supporting tasks. I have defined a batch file .

and triggered it on successful reload of my QVD Generator.

I have defined an OnPostReload Event in my application:

But yet,Only the first part of my macro gets executed properly,cause I can see the hourly data push in the table in Oracle.It somehow doesnt work for the second part ie the second table box.

When I manually execute it ie run the task manually from QMC,I see the data in my table in Oracle.

Pls advise Miguel,

This problem is really killing me..

Thanks.

Re: Issue with Qlikview Publisher

Miguel_Angel_Baeyens — Wed, 12 Feb 2014 07:42:54 GMT

Then it must be something related to the code itself. How are you pushing that data into Oracle? Are you doing functions or subs then calling them from the main one? Or is just a larger piece of code that connects once, pushes the first table, then the second?

Miguel

Re: Issue with Qlikview Publisher

Wed, 12 Feb 2014 10:11:28 GMT

Hi Miguel,

Would it help if I pasted the code script in VB for you here:

SUB VendorMasterPopulate

On Error Resume Next

strDBDesc = "(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=172.18.16.108)(PORT=1521)))(CONNECT_DATA=(SID=orcl)))"

strConnection = "Provider=OraOLEDB.oracle.1;Data Source = " & strDBDesc & ";User ID = '';Password='';"

Dim oCon: SET oCon = CreateObject("ADODB.Connection")

DIM oRs: SET oRS = CreateObject("ADODB.Recordset")

DIM oCommand: SET oCommand=CreateObject("ADODB.Command")

oCon.Open strConnection

oCommand.ActiveConnection = strConnection

DIM RecordsAffected,TotalRowsAffected

DIM vInsertString

vCounter1 = 0

vCounter2 = 0

sqlTxt = "TRUNCATE TABLE tbl_ev_vendor_master"

oCommand.CommandText = sqlTxt

oCommand.Execute

set vendorHeaderTable = ActiveDocument.GetSheetObject("TB01")

set vendorDetailTable = ActiveDocument.GetSheetObject("TB02")

for RowIter = 1 to vendorHeaderTable.GetRowCount-1

vInsertHeader ="INSERT INTO tbl_ev_vendor_master(S_VENDOR_ID,S_VENDOR_NAME,S_ADDRESS,S_CITY,S_STATE,N_PINCODE,S_COUNTRY,N_PHONE_NO,N_FAX_NO,S_EMAIL ,S_VENDOR_ACCNT_GROUP,S_CREATED_BY ,D_CREATED_DATE) VALUES('" &_

vendorHeaderTable.GetCell(RowIter,0).Text & "',q'[" & Replace(vendorHeaderTable.GetCell(RowIter,1).Text,"'"," ' '") & "]',q'[" &_

Replace(vendorHeaderTable.GetCell(RowIter,2).Text,"'","''") &_

"]',q'[" & vendorHeaderTable.GetCell(RowIter,3).Text &_

"]','" & vendorHeaderTable.GetCell(RowIter,4).Text &_

"','" & vendorHeaderTable.GetCell(RowIter,5).Text &_

"','" & vendorHeaderTable.GetCell(RowIter,6).Text &_

"','" & vendorHeaderTable.GetCell(RowIter,7).Text &_

"','" & vendorHeaderTable.GetCell(RowIter,8).Text &_

"','" & vendorHeaderTable.GetCell(RowIter,9).Text &_

"','" & vendorHeaderTable.GetCell(RowIter,10).Text &_

"','" & vendorHeaderTable.GetCell(RowIter,11).Text & "','" & vendorHeaderTable.GetCell(RowIter,12).Text & "')"

SET oRs = oCon.Execute (vInsertHeader,RecordsAffected)

If Err.Number <> 0 Then

msgbox( Err.Description & " " & vInsertHeader)

Err.Clear

On Error Goto 0

End If

TotalRowsAffected = TotalRowsAffected + RecordsAffected

vCounter1 = vCounter1 + 1

vCounter1 = 0

vCounter2 = 0

sqlTxt = "TRUNCATE TABLE TBL_VENDOR_DETAILS_SAP_COPY"

oCommand.CommandText = sqlTxt

oCommand.Execute

for RowIter = 1 to vendorDetailTable.GetRowCount-1

vInsertDetails = "INSERT INTO TBL_VENDOR_DETAILS_SAP_COPY(S_VENDOR_ID,S_PAN_NO,N_CST_NO,N_VAT_NO,N_SERVICETAX_NO,N_ECC_NO,N_SSI_NO,S_CREATED_BY ,D_MODIFIED_DATE,D_CREATED_DATE) VALUES('" &_

vendorDetailTable.GetCell(RowIter,0).Text & "','" & vendorDetailTable.GetCell(RowIter,1).Text & "','" &_

"','" & vendorDetailTable.GetCell(RowIter,2).Text &_

"','" & vendorDetailTable.GetCell(RowIter,3).Text &_

"','" & vendorDetailTable.GetCell(RowIter,4).Text &_

"','" & vendorDetailTable.GetCell(RowIter,5).Text &_

"','" & vendorDetailTable.GetCell(RowIter,7).Text &_

"','" & vendorDetailTable.GetCell(RowIter,8).Text & "','" & vendorDetailTable.GetCell(RowIter,9).Text & "')"

SET oRs = oCon.Execute (vInsertDetails,RecordsAffected)

If Err.Number <> 0 Then

msgbox( Err.Description & " " & vInsertDetails)

Err.Clear

On Error Goto 0

End If

TotalRowsAffected = TotalRowsAffected + RecordsAffected

vCounter1 = vCounter1 + 1

oCon.Close

Set oRs = Nothing

SET oCon = Nothing

END SUB