I have two tables, one of customers, and one of customers I want to exclude from the results.
My script is:
SET NullValue ='<Unknown>';
NULLASVALUE [Cust No], [BaseC];
revenue:
select r.[Cust No], Revenue,
from revenue_report r
LEFT JOIN (revenue)
LOAD [Cust No], [BaseC]
FROM
[Base15.xlsx]
(ooxml, embedded labels, table is Base15);
This brings through all the records, and adds 'BaseC' to any record in the Base15 file (or Null against any that are not).
Simply(!) I want to exclude any [Cust No] in Base15 from the results. I can't find anything anywhere online about how to do this, so I thought I'd try 'excluding' nulls. To do this, I'm trying to give them a value (<unknown>) so there is something to select. But despite the above being exactly what this forum suggests, the 'nullasvalue' script does nothing at all.
Anyone have any ideas, either how to exclude a list, or how to get nullasvalue to work?
maybe it would be possible to first derive a list of customers you want to exclude from some overall list and then use a WHERE NOT EXISTS() clause in the LOAD to exclude those?
Thank you. I'm not entirely sure how I could do that (the definition of the customers I want to keep is the customers not on the loaded list, so I have to refer to the list). I presume something along the lines of:
revenue:
select r.[Cust No], Revenue,
from revenue_report r
LEFT JOIN (revenue)
LOAD [Cust No], [BaseC]
FROM
[Base15.xlsx]
(ooxml, embedded labels, table is Base15);
Base_File:
Load [Cust No] as [Base Cust No]
Resident Base15;
inner Join(Base_File)
Load [Cust No] as [Base Cust No]
Resident revenue;
NB:
Load [Cust No] as [NB Cust No]
Resident revenue
where not exists ([Cust No]);
But that doesn't work as I don't know how to load the fields from my 'revenue' table (it says [Cust No] can't be found).
