topic Re: Data Not Loading As Expected in Connectivity & Data Prep

Data Not Loading As Expected

bruce_sorge — Thu, 17 Mar 2022 19:39:20 GMT

Hello,

I have an app where I am getting data from a spreadsheet and an Oracle database. From the spreadsheet, my load script looks like this:

LOAD
Authors,
"Year",
Title,
Journal,
IF (DOI = ' ', '','https://doi.org/'&DOI) as [HP DOI URL],
DOI as [HO DOI],
"National Research Program",
"Request For Applications (RFA) Title",
"Grant Number",
"Date Highlighted",
"Altmetric Score",
"Research Compass?",
Comments
FROM [lib://Highlighted Pubs (aa_bsorge)]
(ooxml, embedded labels, table is [Highlighted Publications Table]);

The Oracle database load state looks like this (I am only including the table that I am concerned with):

LOAD ABSTRACT_ID,
Keepchar(EPA_ID, '0123456789') as EPA_ID
WHERE Exists([Grant Number], EPA_ID);

So the spreadsheet contains grant numbers that are only numeric, but the Oracle DB table has some preceding letters, so I am stripping them out with the Keepchar() function.

The where clause "should" pull only the records from the Oracle DB that match the spreadsheet (672 records only, not the entire 7K records in the Oracle DB).

I am only getting eight records though which is the problem. I have also tried "Where EPA_ID = [Grant Number], but when I do that, I get the error that Abstract_ID can't be found.

Thanks,

Bruce

Re: Data Not Loading As Expected

Or — Thu, 17 Mar 2022 20:29:51 GMT

Looks like you should be using

WHERE Exists([Grant Number], Keepchar(EPA_ID, '0123456789'));

Within the context of this load, EPA_ID refers to the original column in the source, not the calculated column you've created in the load. This is similar to SQL where you can't reference a column aliased in the SELECT from the WHERE.

Re: Data Not Loading As Expected

bruce_sorge — Fri, 18 Mar 2022 12:01:09 GMT

Hello,

That didn't work, however this did:

abstract:
Load * //PRECEDING LOAD 
 WHERE EXISTS(GrantNumber);//NOW WE CAN SET THE WHERE CONDITION TO EXISTS() MEANING LOAD RECORDS IF GRANTNUMBER OF abstract MATCHES THE PREVIOUS GRANTNUMBER
  LOAD 
      ABSTRACT_ID, 
      text(EPA_ID) as EPA_ID, 
      text(EPA_ID) as GrantNumber; // SETTING THIS AS TEXT TO PROVIDE A STRING MATCH FROM THE PUB TABLE
[ABSTRACT]:
SELECT 
    "ABSTRACT_ID",
    "EPA_ID"
FROM "NCERDB"."ABSTRACT";
//WHERE "EPA_ID" = "GrantNumber";