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,
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
Table1:
Load User, Name from source1;
Table2:
load Db_User from source2;
left join(Table2)
load User as Db_User, Name resident Table1;
or
Table1:
Load User, Name from source1;
Table2:
load Db_User from source2;
left join(Table2)
load User as Db_User, Name as DB_Name resident Table1;
Hi Paolo,
Do this:
Table 2: Load Db_User as User from X (your location) ; Left Join (Table2) Load User Name from X (your location) ;
Jordy
Climber
Thanks all!
This is my real situation:
LOAD
@1 as User,
@2 as Name,
FROM [lib://.../Table1.TXT]
(txt, codepage is 1252, no labels, delimiter is spaces, msq);
LOAD
"DB User Name" as "DB_User",
FROM [lib://..../Table2.csv]
(txt, utf8, embedded labels, delimiter is ',', msq);
__________________
My goal:
If "User" = "DB_User" .... i need to add a field (ex. Owner) in table 2 that contain "Name"
Hi Paolo,
Then you should be able to do this:
Table 2: Load Db_User from X (your location) ; // Do a preceding load so @1 is seen as User and not @1. Left Join (Table 2) Load * Load @1 as Db_User @1 as tmpUser @2 as Name from X (your location) ; FinalTable: Load Db_User IF(Db_User = tmpUser,Name,null()) as Name Resident Table 2 ; drop table Table 2;
Jordy
Climber
Hi Jordy,
i just try but with errors.
Error: Unknown statement: lib://.../Table2.csv Error here: Left Join >>>>>>[lib://.../Table2.csv]<<<<<< Load * Load @1 as Db_User @1 as tmpUser @2 as Name FROM [lib://.../Table2.csv]
My code:
TABLE 1: LOAD @1 as User_Tso, @2 as Name FROM [lib://.../Table1.TXT] (txt, codepage is 1252, no labels, delimiter is spaces, msq); TABLE 2: LOAD "DB User Name" as "DB_User" FROM [lib://.../Table2.csv] (txt, utf8, embedded labels, delimiter is ',', msq); Left Join [lib://.../Table2.csv] Load * Load @1 as Db_User @1 as tmpUser @2 as Name FROM [lib://.../Table2.csv]; FinalTable: Load Db_User IF(Db_User = tmpUser,Name,null()) as Name Resident [lib://.../Table2.csv]; drop table [lib://.../Table2.csv];
Thanks
Hi Paolo,
Yes that is because you need to refer to the Table in Qlik Sense, and not the file.
This is the correct script:
TABLE2: LOAD "DB User Name" as "DB_User" FROM [lib://.../Table2.csv] (txt, utf8, embedded labels, delimiter is ',', msq); Left Join (TABLE2) Load *
; LOAD @1 as DB_User, @1 as tmpUser, @2 as Name FROM [lib://.../Table1.TXT] (txt, codepage is 1252, no labels, delimiter is spaces, msq); FinalTable: Load DB_User, IF(DB_User = tmpUser,Name,null()) as Name Resident TABLE2; drop table TABLE2;
Jordy
Climber
Hi,
now I've this error
Left Join (Table2) Load * >>>>>>LOAD<<<<<< @1 as DB_User, @1 as tmpUser @2 as Name FROM [lib://.../Table1.TXT] (txt, codepage is 1252, no labels, delimiter is spaces, msq)
Hi Paolo,
My fault, a ';' was missing at that spot. This is the new script:
TABLE2: LOAD "DB User Name" as "DB_User" FROM [lib://.../Table2.csv] (txt, utf8, embedded labels, delimiter is ',', msq); Left Join (TABLE2) Load *
; LOAD @1 as DB_User, @1 as tmpUser, @2 as Name FROM [lib://.../Table1.TXT] (txt, codepage is 1252, no labels, delimiter is spaces, msq); FinalTable: Load DB_User, IF(DB_User = tmpUser,Name,null()) as Name Resident TABLE2; drop table TABLE2;
Jordy
Climber
Good morning Jordy,
thank you for your patience.
My answer has been deleted twice. I write it again.
New error:
LOAD @1 as DB_User, @1 as tmpUser >>>>>>@2<<<<<< as Name FROM [lib://Ibm Guardium - Governance (direzione_uga02178)/USTSO.TXT] (txt, codepage is 1252, no labels, delimiter is spaces, msq)
This is a print screen of the real situation, where:
[lib://Ibm Guardium - Governance (direzione_uga02178)/USTSO.TXT] = file of Table1 Comb_Machine_Learning = Table 2
I add the file of the print screen.