Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

cancel
Showing results for 
Search instead for 
Did you mean: 
PaoloGuerra
Creator
Creator

Combine\join 2 table field

Hi all,

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

Table1
UserName

 

Table2
Db_User

 

If Db_User is = User

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

Table 2
Db_UserName

 

Can we help me please?

 

 

 

Labels (2)
1 Solution

Accepted Solutions
JordyWegman
Partner
Partner

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

Work smarter, not harder

View solution in original post

20 Replies
OmarBenSalem
Partner
Partner

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; 

 

 

JordyWegman
Partner
Partner

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

Work smarter, not harder
PaoloGuerra
Creator
Creator
Author

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"

JordyWegman
Partner
Partner

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

Work smarter, not harder
PaoloGuerra
Creator
Creator
Author

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

JordyWegman
Partner
Partner

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

Work smarter, not harder
PaoloGuerra
Creator
Creator
Author

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)
JordyWegman
Partner
Partner

 

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

Work smarter, not harder
PaoloGuerra
Creator
Creator
Author

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.