topic Re: Data from Excel source has to match that found in SQL source data. in QlikView

Data from Excel source has to match that found in SQL source data.

Sat, 23 Jun 2012 12:16:04 GMT

Hi,

In blue below I have an SQL script that ONLY loads Projects that are Horizontal 'ABC'. I've reduced the script considerably for this example. In the red I have text that brings in projects from an Excel document. The problem is that it's bringing in ALL projects! It's cluttering up my QlikView dashboard list with irrelevant projects. How do I effectively say in the load script "Only bring in data from Excel corresponding to Projects that are found in the SQL database"

Thanks to anyone who has any suggestions

ODBC CONNECT TO ABC_Report;

SQL SELECT *

FROM Project.par.Projects

WHERE Status = 'Live'

AND Horizontal = 'ABC'

Project,

[Client (Summary)],

[Client Feedback],

FROM

(ooxml, embedded labels, table is Scorecard);

---------------------------------------------------------------------------------------------------------------

Morrison Govan UK FATCA

Re: Data from Excel source has to match that found in SQL source data.

deepakk — Sat, 23 Jun 2012 12:20:54 GMT

Hi ,

Try the below code.

ODBC CONNECT TO ABC_Report;

SQL SELECT *

FROM Project.par.Projects

WHERE Status = 'Live'

AND Horizontal = 'ABC'

Left join

[Client (Summary)],

[Client Feedback],

FROM

(ooxml, embedded labels, table is Scorecard);

Make sure the field name in the both table which need to be linked has the same name.

Deepak

Re: Data from Excel source has to match that found in SQL source data.

Jason_Michaelides — Sat, 23 Jun 2012 12:34:24 GMT

Assuming "Project" is your ID field present in both data sets, try this:

ODBC CONNECT TO ABC_Report;

SQL SELECT *

FROM Project.par.Projects

WHERE Status = 'Live'

AND Horizontal = 'ABC'

;

Project,

[Client (Summary)],

[Client Feedback],

FROM

(ooxml, embedded labels, table is Scorecard)

Where Exists(Project);

EXISTS will ensure the only records loaded from the Excel file are those whose "Project" value has already been loaded earlier in the script.

Hope this helps,

Jason

Re: Data from Excel source has to match that found in SQL source data.

Jason_Michaelides — Sat, 23 Jun 2012 12:39:02 GMT

Just posted a reply but apparently it has to be approved by a moderator!? I'll try again:

Assuming "Project" is an ID field present in both data sets, put this at te end of your Excel load (before the 😉

WHERE EXISTS(Project);

This will ensure the only rows loaded from Excel are those with a "Project" value that has already been loaded earlier in the script.

Hope this helps,

Jason

Re: Data from Excel source has to match that found in SQL source data.

Sat, 23 Jun 2012 16:54:25 GMT

Thanks Jason, worked perfectly