Combine\join 2 table field

[PaoloGuerra]

Hi all,

i have two table with different fields and I need to join\combine an information.

Table1
User	Name

Table2

Db_User

If Db_User is = User

then  I need to add a new field to table 2 like this:

Table 2
Db_User	Name

Can we help me please?

[JordyWegman]

Hi Paolo,

You only need to add two commas and then it's fixed. 

This is the solution for the first part:

LOAD
@1 as DB_User,
@1 as tmpUser,                     // This is where the comma was needed!
@2 as Name
FROM [lib://Ibm Guardium - Governance (direzione_uga02178)/USTSO.TXT]
(txt, codepage is 1252, no labels, delimiter is spaces, msq)

This is the solution for the second part:

FinalTable:
Load
DB_User,                                      // Add the comma here!
IF(DB_User = tmpUser,Name,null()) as Name
Resident (Comb_Machine_Learning);

drop table (Comb_Machine_Learning);

If it's solved, please mark this post as solved.

Jordy

Climber 

[PaoloGuerra]

Hi Jordy,

unfortunately there is an error about the final table:

Unexpected token: 'LOAD', expected nothing

>>>>>>LOAD<<<<<<
DB_User,                                      
IF(DB_User = tmpUser,Name,null()) as Name
Resident (Comb_Machine_Learning)

[OmarBenSalem]

it's

LOAD
DB_User,                                      
IF(DB_User = tmpUser,Name,null()) as Name
Resident (Comb_Machine_Learning)

not

>>>>>>LOAD<<<<<<
DB_User,                                      
IF(DB_User = tmpUser,Name,null()) as Name
Resident (Comb_Machine_Learning)

[JordyWegman]

Did you add the header? Or did you only start with LOAD?  Because Qlik needs a header for the table.

Paste this (with header!):

FinalTable:
LOAD
DB_User,                                      
IF(DB_User = tmpUser,Name,null()) as Name
Resident (Comb_Machine_Learning)

@OmarBenSalem. the >>>>>LOAD<<<<< is a script error. So he uses LOAD, but I think the header is missing.

[PaoloGuerra]

Ok,

i paste the "real" script with a little explanation.

File of table 1:  lib://Ibm Guardium - Governance (direzione_uga02178)/USTSO.TXT

File of Table 2:  lib://Ibm Guardium - Governance (direzione_uga02178)/Comb_Machine_Learning.csv

Table 2: Comb_Machine_Learning

GOAL:

If "DB_User_Name_Comb_Machine_Learning" is = "User_Tso_from_txt"   then

add a Field (with title Name) in Table 2 that contain  "Cognome_Tit_User_Tso"

SET ThousandSep='.';
SET DecimalSep=',';
SET MoneyThousandSep='.';
SET MoneyDecimalSep=',';
SET MoneyFormat='€ #.##0,00;-€ #.##0,00';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';
SET FirstWeekDay=0;
SET BrokenWeeks=0;
SET ReferenceDay=4;
SET FirstMonthOfYear=1;
SET CollationLocale='it-IT';
SET CreateSearchIndexOnReload=1;
SET MonthNames='gen;feb;mar;apr;mag;giu;lug;ago;set;ott;nov;dic';
SET LongMonthNames='gennaio;febbraio;marzo;aprile;maggio;giugno;luglio;agosto;settembre;ottobre;novembre;dicembre';
SET DayNames='lun;mar;mer;gio;ven;sab;dom';
SET LongDayNames='lunedì;martedì;mercoledì;giovedì;venerdì;sabato;domenica';


LOAD
    "Anomaly Score" as "Anomaly_Score_Comb_Machine_Learning",
    "High volume Outlier" as "High_volume_Outlier_Comb_Machine_Learning",
    "Temp Outlier" as "Temp_Outlier_Comb_Machine_Learning",
    "New Outlier" as "New_Outlier_Comb_Machine_Learning",
    "Diverse Outlier" as "Diverse_Outlier_Comb_Machine_Learning",
    "Error Outlier" as "Error_Outlier_Comb_Machine_Learning",
    "Ongoing Outlier" as "Ongoing_Outlier_Comb_Machine_Learning",
    "Server IP" as "Server_IP_Comb_Machine_Learning",
    "DB Name" as "DB_Name_Comb_Machine_Learning",
    "DB User Name" as "DB_User_Name_Comb_Machine_Learning",
    "Privileged User" as "Privileged_User_Comb_Machine_Learning",
    "Period Start" as "Period_Start_Comb_Machine_Learning"
FROM [lib://Ibm Guardium - Governance (direzione_uga02178)/Comb_Machine_Learning.csv]
(txt, utf8, embedded labels, delimiter is ',', msq);


LOAD
    @1 as User_Tso_from_txt,
    @2 as Cognome_Tit_User_Tso,
    @3 as Nome_Tit_User_Tso
FROM [lib://Ibm Guardium - Governance (direzione_uga02178)/USTSO.TXT]
(txt, codepage is 1252, no labels, delimiter is spaces, msq);


LOAD
@1 as User_Tso_from_txt,
@1 as tmpUser,                     // This is where the comma was needed!
@2 as Cognome_Tit_User_Tso
FROM [lib://Ibm Guardium - Governance (direzione_uga02178)/USTSO.TXT]
(txt, codepage is 1252, no labels, delimiter is spaces, msq)

LOAD
DB_User_Name_Comb_Machine_Learning,                                      // Add the comma here!
IF(DB_User_Name_Comb_Machine_Learning = tmpUser,Cognome_Tit_User_Tso,null()) as Cognome_Tit_User_Tso
Resident (Comb_Machine_Learning);

drop table (Comb_Machine_Learning);

[JordyWegman]

Hi Paolo,

Try this script:

SET ThousandSep='.';
SET DecimalSep=',';
SET MoneyThousandSep='.';
SET MoneyDecimalSep=',';
SET MoneyFormat='€ #.##0,00;-€ #.##0,00';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';
SET FirstWeekDay=0;
SET BrokenWeeks=0;
SET ReferenceDay=4;
SET FirstMonthOfYear=1;
SET CollationLocale='it-IT';
SET CreateSearchIndexOnReload=1;
SET MonthNames='gen;feb;mar;apr;mag;giu;lug;ago;set;ott;nov;dic';
SET LongMonthNames='gennaio;febbraio;marzo;aprile;maggio;giugno;luglio;agosto;settembre;ottobre;novembre;dicembre';
SET DayNames='lun;mar;mer;gio;ven;sab;dom';
SET LongDayNames='lunedì;martedì;mercoledì;giovedì;venerdì;sabato;domenica';


//=================================================================================================
// Load the first table with all the data
//=================================================================================================

Table2
LOAD
    "Anomaly Score" as "Anomaly_Score_Comb_Machine_Learning",
    "High volume Outlier" as "High_volume_Outlier_Comb_Machine_Learning",
    "Temp Outlier" as "Temp_Outlier_Comb_Machine_Learning",
    "New Outlier" as "New_Outlier_Comb_Machine_Learning",
    "Diverse Outlier" as "Diverse_Outlier_Comb_Machine_Learning",
    "Error Outlier" as "Error_Outlier_Comb_Machine_Learning",
    "Ongoing Outlier" as "Ongoing_Outlier_Comb_Machine_Learning",
    "Server IP" as "Server_IP_Comb_Machine_Learning",
    "DB Name" as "DB_Name_Comb_Machine_Learning",
    "DB User Name" as "DB_User_Name_Comb_Machine_Learning",
    "Privileged User" as "Privileged_User_Comb_Machine_Learning",
    "Period Start" as "Period_Start_Comb_Machine_Learning"
FROM [lib://Ibm Guardium - Governance (direzione_uga02178)/Comb_Machine_Learning.csv]
(txt, utf8, embedded labels, delimiter is ',', msq);

//=================================================================================================
// Left join the tmpDB user and Cognome. The tmpDB is needed for the comparison in the if statement
//=================================================================================================

Left Join (Table2)
LOAD
	*
;
LOAD
    @1 as DB_User_Name_Comb_Machine_Learning,
	@1 as tmpDB_User_Name_Comb_Machine_Learning,
    @2 as Cognome_Tit_User_Tso
FROM [lib://Ibm Guardium - Governance (direzione_uga02178)/USTSO.TXT]
(txt, codepage is 1252, no labels, delimiter is spaces, msq);

//=================================================================================================
// Resident load the Table2 you have created with the addition of tmpDB and Cognome. Make the if statement.
//=================================================================================================

FinalTable:
LOAD
	DB_User_Name_Comb_Machine_Learning,                                      
	IF(DB_User_Name_Comb_Machine_Learning = tmpDB_User_Name_Comb_Machine_Learning,Cognome_Tit_User_Tso,null()) as Cognome_Tit_User_Tso
Resident (Table2);

//=================================================================================================
// Drop the field and table that are not needed anymore
//=================================================================================================

drop field tmpDB_User_Name_Comb_Machine_Learning from Table2;
drop table Table2;

I added also the explanation.

Jordy

Climber

[PaoloGuerra]

Hi Jordy.

Unexpected token: 'drop', expected nothing

Error here:
LOAD
	DB_User_Name_Comb_Machine_Learning,                                      
	IF(DB_User_Name_Comb_Machine_Learning = tmpDB_User_Name_Comb_Machine_Learning,Cognome_Tit_User_Tso,null()) as Cognome_Tit_User_Tso
Resident Comb_Machine_Learning

>>>>>>drop<<<<<< field tmpDB_User_Name_Comb_Machine_Learning from Comb_Machine_Learning

[JordyWegman]

Hi Paolo,

What happens if you delete that line? 

drop field tmpDB_User_Name_Comb_Machine_Learning from Comb_Machine_Learning   

 Jordy

Climber

[PaoloGuerra]

Good morning Jordy,

if I delete the line, I have di same error but on the next line.

Unexpected token: 'drop', expected nothing

Error here:
LOAD
	DB_User_Name_Comb_Machine_Learning,                                      
	IF(DB_User_Name_Comb_Machine_Learning = tmpDB_User_Name_Comb_Machine_Learning,Cognome_Tit_User_Tso,null()) as Cognome_Tit_User_Tso
Resident Comb_Machine_Learning





>>>>>>drop<<<<<< table Comb_Machine_Learning

[JordyWegman · Accepted solution]

Hi Paolo,

And what happens if you also delete the last line? Do you get a synthetic key? Qlik Sense will tell  you this at the end of the script.

Jordy

Climber