Qlik Community

Ask a Question

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
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

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

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

Not applicable

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?