topic Re: Unable to incorporate data file with SQL Select in QlikView

Unable to incorporate data file with SQL Select

Thu, 30 Jun 2016 18:04:17 GMT

Hello,

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.

Re: Unable to incorporate data file with SQL Select

Peter_Cammaert — Thu, 30 Jun 2016 18:50:55 GMT

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.

Best,

Peter

Re: Unable to incorporate data file with SQL Select

Thu, 30 Jun 2016 20:08:53 GMT

Thank you, I'll give this a try in the morning. I appreciate the help guys!

Re: Unable to incorporate data file with SQL Select

Tue, 05 Jul 2016 12:45:21 GMT

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?