topic Re: SQL Query with Excel exceptions in QlikView

SQL Query with Excel exceptions

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

Hi, everyone!

I need to make a select in a SQL table excluding the codes that are in an already loaded Excel.
Example:

Excel (lonely column)
Code
1
4
6
9
10
67 (... almost 400 codes)

SQL Server table
Code - Name
1 - Jose
1 - Maria
3 - Maria
3 - Carlos
8 - Joao
10 - Joao
10 - Pedro
65 - Carlos
67 - Carlos (... a lot of lines)

I need something like "Select * From SQL_Table Where Code Not Exists In Excel_Table".

I'm migrating an application that was in PowerBI originally, so I'm not very close to the QlikView scripting functions.

Thaks a lot!

Re: SQL Query with Excel exceptions

Nicole-Smith — Mon, 23 Sep 2019 21:37:12 GMT

Something like this should do it:

ExcludeCodeList: LOAD Code FROM YourExcelFile.xlsx; CodeFinal: LOAD * WHERE NOT EXISTS(Code); SELECT Code, Name FROM YourSQLTable; DROP TABLE ExcludeCodeList;

Re: SQL Query with Excel exceptions

CharlesNaseh — Tue, 24 Sep 2019 12:44:24 GMT

Hi, Nicole.

Thank you for your answer. The result was something weird - only a low percent of lines should be excluded, but the final result was too low. I'll analyze the tables. May I ask you just one more question?

I have this case:

I need to make a third grid = (first grid - second grid), where date = today and group by each letter (that is a group of specified cars - the first grid is all my cars available and the second grid the cars picked by hour today and tomorrow). I need to see if I'll have enough cars today. My set analysis filters are not working. The third grid must be like this:
AM - 143 (492 from the first table minus 349 from the second)
AT - 54 (87 - 33)
B - 1089 (...)
and so on...

Each grid was filled from two distincts Selects in the script.

May you give me some idea?

Thanks again! 😉

Re: SQL Query with Excel exceptions

Nicole-Smith — Tue, 24 Sep 2019 19:35:55 GMT

Can you post a sample file? Hard to tell what you need to do without seeing data and data model.

Re: SQL Query with Excel exceptions

CharlesNaseh — Tue, 24 Sep 2019 20:07:02 GMT

Hi, again, Nicole!

I solved this problem filtering date with set analysis and creating a syn key with "group" and other fields I needed.
Sorry for the poor information before, but thank you so much for all your attention!

😉

Re: SQL Query with Excel exceptions

CharlesNaseh — Tue, 24 Sep 2019 20:14:34 GMT

Oh, and about the first problem, I solved using different names for each field, like this:

load * where not exists (filter_field, filtered_field);

Your help was essential!!!