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

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

Sat, 23 Jun 2012 12:12:36 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 sourceda has to match that found in SQL source data.

chematos — Mon, 25 Jun 2012 20:26:54 GMT

Hi, I´m going to think that there is a field Project from the SQL table so I´m going to do a left join by this field with the table from the Excel sheet so you will have only data of those projects.

Finnally, you can get the fields of the excel but taking them from the table just created so you will have the two tables separated but linked by Project.

ODBC CONNECT TO ABC_Report;

Projects_table:

LOAD *;

SQL SELECT *

FROM Project.par.Projects

WHERE Status = 'Live'

AND Horizontal = 'ABC'

Left Join(Projects_Table)

Project,

[Client (Summary)],

[Client Feedback],

FROM

(ooxml, embedded labels, table is Scorecard);

Table_Excel:

Load

Project,

[Client (Summary)],

[Client Feedback],

Resident Projects_table;

Drop Fields [Client (Summary)], [Client Feedback] from Projects_table;

Hope this helps.

Regards,

Chema

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

Jason_Michaelides — Mon, 25 Jun 2012 21:54:19 GMT

Didn't I already answer this here - http://community.qlik.com/message/231318#231318 ?