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!
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!
😉
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!!!
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;
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! 😉
Can you post a sample file? Hard to tell what you need to do without seeing data and data model.
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!
😉
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!!!