I am working on a project that requires the addition, update, and delete of field using Qlikview as part of an automation initative in my company.
As of right now I have imported an excel extract that contains NEW data based on a primary key called [ID], I have also imported a SQL table with HISTORICAL values, the primary key is [ID] as well. The problem is I am trying to delete values in the SQL Historical Table that match [ID] with New values on the excel extract so I can insert all new values into the historical table without getting duplicates. I have considered update but it is more complex and there are new records occasionally that are in the extract but not in historical that need to be added to historical.
Here is what I have so far...
SQL DELETE [ID]
FROM "DB NAME HERE".dbo."TABLE NAME HERE"
WHERE [TABLE NAME HERE.Known Error ID] IN extract file
In bold is the problem, any suggestions? I had to censor some db information for company sake.
Put all IDs to delete from your Excel extract file as a comma-separated list into a variable. Then use that variable in a $-sign expansion in your SQL statement to pass a list of IDs to delete to the DB server.
If your IDs are alphanumeric, you may want to add quotes to each value. Or whatever SQL requires to specify string literals.
I've tried this method but I realized that the list is excel sheet has [ID's] that are dynamic and change everytime the job runs every month, the excel extract also has other fields aside from the ID field so making a comma separated list would not be sufficient. There must be a way to parse out only the ID's and pass those to a CSV?