topic Re: Using where exists in sql with data from excel table in QlikView

Using where exists in sql with data from excel table

sarahshong — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Using where exists in sql with data from excel table

vishsaggi — Thu, 26 Jul 2018 19:58:39 GMT

Try this?

LOAD

Date

FROM ExcelTable;

LOAD *

WHERE EXISTS (Date);

SQL Select

Name

Date

From Database.Table

;

Re: Using where exists in sql with data from excel table

sarahshong — Thu, 26 Jul 2018 20:22:43 GMT

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.

Re: Using where exists in sql with data from excel table

CarlosAMonroy — Thu, 26 Jul 2018 20:37:01 GMT

Hi Sarah,

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

Hope that helps,

Carlos M

Re: Using where exists in sql with data from excel table

vishsaggi — Thu, 26 Jul 2018 20:54:50 GMT

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.

Re: Using where exists in sql with data from excel table

sarahshong — Fri, 27 Jul 2018 16:14:15 GMT

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.

Re: Using where exists in sql with data from excel table

vishsaggi — Fri, 27 Jul 2018 16:17:07 GMT

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

Re: Using where exists in sql with data from excel table

sarahshong — Fri, 27 Jul 2018 18:20:21 GMT

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