Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
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)
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)
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.
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);
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
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
Hi Paolo,
What happens if you delete that line?
drop field tmpDB_User_Name_Comb_Machine_Learning from Comb_Machine_Learning
Jordy
Climber
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
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