Using where exists in sql with data from excel table

[sarahshong]

Hello,

I am trying to limit my sql load from the database based on fields that exists in an excel table.

Excel Table:

20180101

20180201

20180301

SQL Database Table:

Name

Date

I want to limit the names that get pulled based on Date which is limited to Dates in the excel table. I have a very large amount of data in the SQL Database table and so using a WHERE EXISTS clause in the Qlikview load section would still require a large amount of time.

Something like the below, but I understand my exisiting format doesn't work because the WHERE EXIST function does not work this way in SQL.

I also can write out each date in the Excel Table using  IN () because the dates that exist in the excel file change based on the current date.

LOAD

Date

FROM ExcelTable;

LOAD *

SQL Select

Name

Date

From Database.Table

WHERE EXISTS (Date);

Appreciate the help in advance!

Sarah

[vishsaggi]

Try this?

LOAD

Date

FROM ExcelTable;

LOAD *

WHERE EXISTS (Date);

SQL Select

Name

Date

From Database.Table

;

[sarahshong]

Hi Vishwarath,

I want to limit the amount of data that is originally being pulled from the SQL database because the size of the database is so large. If I use the where exists function in the Qlikview Load like you've shown above, it will still take a very long time.

[CarlosAMonroy]

Hi Sarah,

May be you can create min and max variables and pass them through SQL using where clause.

Hope that helps,

Carlos M

[vishsaggi · Accepted solution]

Not sure of better way of doing this, but probably two suggestions can be like

Import your excel data into your database and create a table in your SQL database. Then use like

1st Way:

LOAD *

;

SQL Select

Name

Date

From Database.Table

WHERE Date IN (SELECT Date FROM yourexcelFilename);

2nd Way: Refering to Carlos suggestion you can try like

Excel:

LOAD Date

FROM ExcelTable;

MinMaxDate:

LOAD Min(Date) AS MinDate,

           Max(Date) AS MaxDate

Resident Excel;

SET vMinDate = Peek('MinMaxDate', MinDate);

SET vMaxDate = Peek('MinMaxDate', MaxDate);

Drop Tables MinMaxDate, Excel;

LOAD *;

SQL Select Date, Name

FROM Database.tablename

WHERE Date Between '$(vMinDate)' AND '$(vMaxDate)' ;

Modify accordingly if you face any syntax errors or issues.

[sarahshong]

Thank you all for the suggestions. I ended up doing a hybrid and creating a variable for each date in my excel table using the peek function. Then I applied the variable in the SQL WHERE IN() clause.

The reason I couldn't do a min/max was because the range of the dates was too large.

[vishsaggi]

Did you create one variable for each date? How many dates were there to look for in IN clause?

[sarahshong]

Yes, 1 variable for each date. There were only 6 rolling dates in the Excel file. Thanks again!