Linking 2 tables in Qlik Sense

[Tribhuwan]

Hello Everyone,

I am new to Qlik Sense and need your help.

I have 2 tables, in first table I have EMP_ID and EMP_Name and other details and in second table I have EMP_ID, EMP_Name, Departments, Manager_Name etc. Now I want to join these 2 tables so that I have the Departments, Manager_Name and other information for EMP_ID's in first table.

My first table has around 2k EMP_ID's and my second table has around 40k records.

So in my final table I only want data for these 2k records and not the entire data.

Please help me out

[klikgevoel]

The question is a bit ambiguous but let's try figuring it out. So you want to reduce your data, according to the smallest table; the first one?

The question is, what data do you want to retain? The oldest or newest? I assume that the second table has a historic data, since > 2k of records. Second question that comes into mind, which table has the current data?

If that's the case you can apply a Left Join() and use LastValue() as an aggregation clause, followed by a Group By() on EMP_ID.

Example:

Table1:

LOAD

EMP_ID,

EMP_Name

FROM DataSource;

Left Join(Table1)

LOAD

EMP_ID,

LastValue(EMP_Name) As LastEMP_Name, //You can leave this one out if EMP_Name from Table1 suffices

LastValue(Manager_Name) As LastManager_Name

LastValue(Departments) As Departments

FROM DataSource

Group By(EMP_ID);

[Tribhuwan]

Yes you are right in saying that I need details for only 2k accounts.

I think I have not explained it properly (apologies for same), my first data set is in excel and second one larger database is in SAP HANA which I am loading through SQL.

Second table in SAP HANA has all the information historic as well as current as its has information of all the employees in different offices across the globe, whereas excel file has data for only one single office.

So I need to extract the data for these 2k employees from SAP HANA database.

Please let me know if you need further information but please help me out as I am stuck at this point.

[klikgevoel]

For your excel table, the one with 2k accounts, you could easily load this into Qlik via script editor or otherwise. The larger SQL table you can also load into Qlik via an ODBC Connector, which comes with the native connectors in Qlik, see https://help.qlik.com/en-US/connectors/Subsystems/ODBC_connector_help/Content/Connectors_ODBC/SQLSer... 

My approach would be:

1. Load in the excel file

2. Load in all the SQL data via the connection to the SQL db

3. And left join step 2 into 1, only on the primary key EMP_ID.