Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Unable to incorporate data file with SQL Select

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.

3 Replies
Peter_Cammaert
Partner
Partner

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

Not applicable
Author

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

Not applicable
Author

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?