Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear community,
Is it possible for QlikView to delete row in datamart?
Requirement:
1. select few rows in list box
2. create a delete button, once press/trigger, activate delete in the script:
TableBlaBla:
DIRECT DELETE
ABC
FROM TableBlaBla
WHERE ABC = 'haha';
Anyone try this out and it is working?
Thanks and best regards,
Chanel
Hi Chanel,
From my past experiences we can use to insert, delete from a SQL Server but on Oracle it fails.
To Achieve the above you can write a Macro and can execute it on the button click.
Function DeleteData(ValuesToDelete)
dim sConn, oConn
Set oConn = CreateObject("ADODB.Connection")
sConn = "Provider=MSDAORA;User ID="yourlogin";Password="yourpassword";Data Source="your data source";OLEDB.NET=true;SPPrmsLOB=true;Persist Security Info=False;"
oConn.Open sConn
sSQL="Delete from Tablename Where FieldName in " & ValuesToDelete
oConn.Execute(sSQL)
oConn.Close
Set oConn = Nothing
End Function
You will need to enable the "Script (Allow Database Write and Execute Statements)" property inside User Preferences -> Security.
Hope this helps.
Thanks
Hi
upto my knowledge it is not possible in qlikview
Chanel
I would be very wary of using QlikView to write back to the database. Direct Discovery was not designed for this, indeed I have seen QlikTech advise using a read only database connection.
Notwithstanding I get asked about this kind thing every now & then, and if you give it a try I'd be interested in your experiences.
Best Regards, Bill
I don`t believe it`s possible, since the qlikview is a tool for data analysis.
Thanks pretty much! I will test this out once my datamart is ready.
Dear Michael,
I got this error : Invalid connection string attribute, do you have any idea on this?
Thanks and best regards,
Chanel
Hi Chanel,
Did you resolve this ? I too had issues to delete table in MS access, but tried using excel vba to do the same and used the same connection string in QV and it worked.
see the example below
fnm=ActiveDocument.Variables("vfiles").GetContent().String
AppPath = "S:\Phase I\After Run1\Database v1.1.accdb"
Set objADO = CreateObject("ADODB.Connection")
objADO.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & AppPath & ";"
objADO.Execute("delete * from Basedata where Filename="&chr(34)& fnm &chr(34))
objADO.Close
Basically test the connection in Excel or other application and bring it to the QV. It should work.
kamal
Another example:
Sub DeleteFrom_Oracle_DB
Dim objADO
Dim strSQL
'Create ADO Object
Set objADO = CreateObject("ADODB.Connection")
'Establish a connection: See https://www.connectionstrings.com/oracle/ for valid connection types
objADO.Open "Provider=OraOLEDB.Oracle;Data Source=<service_name>;User Id=<user>[<schema>];Password=<password>"
'Set SQL to e.g. delete rows
strSQL="DELETE FROM <schema>.<table> WHERE <field> LIKE '<your_value>'"
'Begin Transaction
objADO.BeginTrans
'Execute Transaction
objADO.Execute strSQL, RecordsAffected
objADO.CommitTrans
objADO.Close
Msgbox RecordsAffected
End Sub