How to concatenate my two tables - Qlik Community

wholelottablue · ‎2019-11-11

Hi I'm trying to concatenate 2 newly made tables from Qlik Sense into one table. I'm doing all of this on the data load editor. After that I want to clean up the field names to make it look neat. Any help? THANK YOU!

Table 1: (Mix of PIV table and a field from Users 1)

[PIV]:
LOAD
[Username] AS [PIV.EmailAddress],
[Realm] AS [PIV.Realm],
[Login Time] AS [PIV.Login Time],
[Device ID] AS [PIV.Device ID],
[Role] AS [PIV.Role],
[Device OS] AS [PIV.Device OS],
[Office] AS [PIV.Office],
[Office2] AS [PIV.Office2]
FROM [lib://AttachedFiles/example file.xlsx]
(ooxml, embedded labels, table is PIV);

[Users1]:
Left Join
LOAD
[Email] AS [PIV.EmailAddress],
[Username]
FROM [lib://AttachedFiles/example user file.xlsx]
(ooxml, embedded labels, table is Users);

Table 2: (Mix of PIV and a field from Users1)

[PIV]:
LOAD
[Username] AS [PIV.EmailAddress],
[Realm] AS [PIV.Realm],
[Login Time] AS [PIV.Login Time],
[Device ID] AS [PIV.Device ID],
[Role] AS [PIV.Role],
[Device OS] AS [PIV.Device OS],
[Office] AS [PIV.Office],
[Office2] AS [PIV.Office2]
FROM [lib://AttachedFiles/example file.xlsx]
(ooxml, embedded labels, table is PIV);

[Users1]:
Left Join
LOAD
[Email] AS [PIV.EmailAddress],
[Username]
FROM [lib://AttachedFiles/example user file.xlsx]
(ooxml, embedded labels, table is Users);

Lech_Miszkiewicz · ‎2019-11-11

Table 1: (Mix of PIV table and a field from Users 1)

[PIV]:
LOAD
[Username] AS [PIV.EmailAddress],
[Realm] AS [PIV.Realm],
[Login Time] AS [PIV.Login Time],
[Device ID] AS [PIV.Device ID],
[Role] AS [PIV.Role],
[Device OS] AS [PIV.Device OS],
[Office] AS [PIV.Office],
[Office2] AS [PIV.Office2]
FROM [lib://AttachedFiles/example file.xlsx]
(ooxml, embedded labels, table is PIV);

// concatenate second table

Concatenate(PIV)

LOAD
[Username] AS [PIV.EmailAddress],
[Realm] AS [PIV.Realm],
[Login Time] AS [PIV.Login Time],
[Device ID] AS [PIV.Device ID],
[Role] AS [PIV.Role],
[Device OS] AS [PIV.Device OS],
[Office] AS [PIV.Office],
[Office2] AS [PIV.Office2]
FROM [lib://AttachedFiles/example file.xlsx]
(ooxml, embedded labels, table is PIV);

// join at the end:

Left Join (PIV)
LOAD
[Email] AS [PIV.EmailAddress],
[Username]
FROM [lib://AttachedFiles/example user file.xlsx]
(ooxml, embedded labels, table is Users);

cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.