Left join error "Field not found"

[PaoloGuerra]

Hi all,

I have 2 tables with fields:

My goal is:

IF "DB_Name_Daily_Outlier"  =  "DB_App"  put the value of "Application" in "Outlier_Last_Daily_Report"

// Load table Outlier_Last_Daily_Report

LOAD
    "Anomaly Score" as "Anomaly_Score_Daily_Outlier",
    "High volume Outlier" as "High_volume_Daily_Outlier",
    "Temp Outlier" as "Temp_Daily_Outlier",
    "New Outlier" as "New_Daily_Outlier",
    "Diverse Outlier" as "Diverse_Daily_Outlier",
    "Error Outlier" as "Error_Daily_Outlier",
    "Ongoing Outlier" as "Ongoing_Daily_Outlier",
    "Server IP" as "Server_IP_Daily_Outlier",
    "DB Name" as "DB_Name_Daily_Outlier",
    "DB User Name" as "DB_User_Name_Daily_Outlier",
    "Privileged User" as "Privileged_User_Daily_Outlier",
    "Period Start" as "Period_Start_Daily_Outlier"
FROM [lib://Ibm Guardium - Governance (direzione_uga02178)/Outlier_Last_Daily_Report.csv]
(txt, utf8, embedded labels, delimiter is ',', msq, no eof);

\\ Load APP_TABLE

LOAD
    "Application",
    DB_App,
    Tecnology,
    Notes
FROM [lib://Ibm Guardium - Governance (direzione_uga02178)/APP_TABLE.xlsx]
(ooxml, embedded labels, table is APP_TABLE);

\\ Left Join

Left Join (APP_TABLE)
LOAD
	*
;
LOAD
    DB_App as DB_Name_Daily_Outlier,
    DB_App as tmpDB_Name_Daily_Outlier,
    "Application"
   FROM [lib://Ibm Guardium - Governance (direzione_uga02178)/APP_TABLE.xlsx]
(txt, codepage is 1252, no labels, delimiter is spaces, msq);

LOAD
	DB_Name_Daily_Outlier,                                      
	IF(DB_Name_Daily_Outlier = tmpDB_Name_Daily_Outlier,"Application",null()) as "Application"
Resident Outlier_Last_Daily_Report;

The error is: Field not found - <DB_App>

\\ERROR

LOAD
    DB_App as DB_Name_Daily_Outlier,
    DB_App as tmpDB_Name_Daily_Outlier,
    "Application"
   FROM [lib://Ibm Guardium - Governance (direzione_uga02178)/APP_TABLE.xlsx]
(txt, codepage is 1252, no labels, delimiter is spaces, msq)

Help me, please.

[pradosh_thakur]

---

\\ERROR

LOAD
    DB_App as DB_Name_Daily_Outlier,
    DB_App as tmpDB_Name_Daily_Outlier,
    "Application"
   FROM [lib://Ibm Guardium - Governance (direzione_uga02178)/APP_TABLE.xlsx]
(txt, codepage is 1252, no labels, delimiter is spaces, msq)

Are you sure about the " NO LABELS".. Check and may be change to something else like embedded label if suitable.

---

[PaoloGuerra]

Hi @pradosh_thakur ,

if I load the table without " " the error is the same

\\ APP_TABLE

LOAD
    Application,
    DB_App,
    Tecnology,
    Notes
FROM [lib://Ibm Guardium - Governance (direzione_uga02178)/APP_TABLE.xlsx]
(ooxml, embedded labels, table is APP_TABLE);

ERROR 

Field not found - <DB_App>

LOAD
    DB_App as DB_Name_Daily_Outlier,
    DB_App as tmpDB_Name_Daily_Outlier,
    Application
   FROM [lib://Ibm Guardium - Governance (direzione_uga02178)/APP_TABLE.xlsx]
(txt, codepage is 1252, no labels, delimiter is spaces, msq)

[pradosh_thakur]

i was asking to change this
(txt, codepage is 1252, no labels, delimiter is spaces, msq)

to

(txt, codepage is 1252, embedded labels, delimiter is spaces, msq)

[PaoloGuerra]

Hi @pradosh_thakur ,

same error:

Field not found - <DB_App>

\\ error
LOAD
    DB_App as DB_Name_Daily_Outlier,
    DB_App as tmpDB_Name_Daily_Outlier,
    Application
   FROM [lib://Ibm Guardium - Governance (direzione_uga02178)/APP_TABLE.xlsx]
(txt, codepage is 1252, embedded labels, delimiter is spaces, msq)

[pradosh_thakur]

Can you check for spaces and case sensitivity in the APP_TABLE.xlsx?

[PaoloGuerra]

I think there is nothing special

Application	DB_App	Tecnology	Notes
Liquido	SINIPROD	ORACLE	Database Schemi Primari
Liquido	SINISPPR	ORACLE	Database Schemi di Supporto
Liquido	SINIDGPR	ORACLE	Database copia Real Time del Primario
CRM	MDMPROD	DB2	Online
CRM	STGPROD	DB2	Batch
CRM	GTEPROD	DB2	Google search
CRM	PSEPROD	DB2	Printer server
CRM	MSGPROD	DB2	Message NOL
CRM	DWCRPROD	ORACLE	Comunicazione DWH
CRM	QBAGPROD	ORACLE	Query builder
CRM	QUADPROD	ORACLE	Quaderni
CRM	STAPPROD	ORACLE	Statistiche
CRM	MKAPROD	DB2	Marketing automation CRM-Analitico
CRM	P7COMMDB	DB2	Portal server
CRM	P7CUSTDB	DB2	Portal server
CRM	P7FDBKDB	DB2	Portal server
CRM	P7JCRDB	DB2	Portal server
CRM	P7LMDB	DB2	Portal server
CRM	P7RELDB	DB2	Portal server

[pradosh_thakur · Accepted solution]

try reloading the only sheet as it is . Then copy paste the code. sometimes the leading or trailing space might create the issue. Just a guess . There is no reason why it should not work.

[PaoloGuerra]

Hi @pradosh_thakur ,

it work if I convert and load the file in format txt. 

Now I think there is a problem on .xlsx