Left Join not work

[PaoloGuerra]

Hi all,

I've 2 tables with fields.

My goal is:

If DB_User_Name_Daily_Outlier = sAMAccountName  put DisplayName into table Outlier_Last_Daily_Report

This is the script:

//=================================================================================================
// 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);

//=================================================================================================
// END LOAD TABLE Outlier_Last_Daily_Report
//=================================================================================================
//=================================================================================================
// LOAD TABLE ExportUsers AND LEFT JOIN 
//=================================================================================================
LOAD
    sAMAccountName,
    DisplayName
FROM [lib://Ibm Guardium - Governance (direzione_uga02178)/ExportUsers.csv]
(txt, utf8, embedded labels, delimiter is ';', msq);


Left Join (Outlier_Last_Daily_Report)
LOAD
	*
;
LOAD
    sAMAccountName as DB_User_Name_Daily_Outlier,
    sAMAccountName as tmpDB_User_Name_Daily_Outlier,
    DisplayName
   FROM [lib://Ibm Guardium - Governance (direzione_uga02178)/ExportUsers.csv]
(txt, codepage is 1252, no labels, delimiter is spaces, msq);

LOAD
	DB_User_Name_Daily_Outlier,                                      
	IF(DB_User_Name_Daily_Outlier = tmpDB_User_Name_Daily_Outlier,DisplayName,null()) as DisplayName
Resident Outlier_Last_Daily_Report;

The error is:

Field not found - <sAMAccountName>

ERROR:
LOAD
    sAMAccountName as DB_User_Name_Daily_Outlier,
    sAMAccountName as tmpDB_User_Name_Daily_Outlier,
    DisplayName
   FROM [lib://Ibm Guardium - Governance (direzione_uga02178)/ExportUsers.csv]
(txt, codepage is 1252, no labels, delimiter is spaces, msq)

Can you help me please?

[lfetensini]

Use ApplyMap techs:

1) Load your Table for mapping:

Mapping_ExportUsers:
Mapping Load
sAMAccountName as Mapping_ExportUsers1,
DisplayName as Mapping_ExportUsers2
FROM [lib://Ibm Guardium - Governance (direzione_uga02178)/ExportUsers.csv]
(txt, utf8, embedded labels, delimiter is ';', msq);


2) In some part from other table, call to Apply:

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",
ApplyMap('Mapping_ExportUsers', "DB User Name") as DisplayName, // HERE !!
"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);

[PaoloGuerra]

Hi @lfetensini ,

without IF condition?

Thanks

[lfetensini]

ApplyMap do all the "if" job. If the target do a "Match", then apply second column. Else, Null().

If you need a "Else" statment, then do that:

ApplyMap('tablemapping', [fieldtoapply], elsecondition)

[PaoloGuerra]

Hi @lfetensini ,

not work and i think this create a problem into other part of the code:

The script:

// THE MAPPING

Mapping_ExportUsers:
Mapping Load
sAMAccountName as Mapping_ExportUsers1,
DisplayName as Mapping_ExportUsers2
FROM [lib://Ibm Guardium - Governance (direzione_uga02178)/ExportUsers.csv]
(txt, utf8, embedded labels, delimiter is ';', msq);

// LOAD TABLE Outlier_Last_Daily

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",
    ApplyMap('Mapping_ExportUsers', "DB User Name") as DisplayName, // HERE !!
    "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);

// START other join table

Left Join (Outlier_Last_Daily_Report)
LOAD
	*
;
LOAD
    @1 as DB_User_Name_Daily_Outlier,
    @1 as tmpDB_User_Name_Daily_Outlier,
    @2 as Day_Cognome_Tit_User_Tso,
    @3 as Day_Nome_Tit_User_Tso
   FROM [lib://Ibm Guardium - Governance (direzione_uga02178)/USTSO2.TXT]
(txt, codepage is 1252, no labels, delimiter is spaces, msq);

LOAD
	DB_User_Name_Daily_Outlier,                                      
	IF(DB_User_Name_Daily_Outlier = tmpDB_User_Name_Daily_Outlier,Day_Cognome_Tit_User_Tso,null()) as Day_Cognome_Tit_User_Tso
Resident Outlier_Last_Daily_Report;

LOAD
	DB_User_Name_Daily_Outlier,                                      
	IF(DB_User_Name_Daily_Outlier = tmpDB_User_Name_Daily_Outlier,Day_Nome_Tit_User_Tso,null()) as Day_Nome_Tit_User_Tso
Resident Outlier_Last_Daily_Report;  

ERROR

Table 'Outlier_Last_Daily_Report' not found

\\ ERROR 

Left Join (Outlier_Last_Daily_Report)
LOAD
	*

Thanks