topic Combining Columns from Two Tables in QlikView

Combining Columns from Two Tables

PCullinan — Wed, 25 Nov 2020 16:16:04 GMT

Hello,

I'm relatively new to Qlikview and hoping someone can help me with a scripting issue I'm having. I have a table structured as follows though with a large number of records:

Table1:

LOAD * INLINE [
Role, ID
Manager, 1153
Employee, 1154
Employee, 1155
Employee, 1156
];

I have another table that is structured as follows:

Table:2

LOAD * INLINE [
Org_CD, Org_Name
1, Org1
2, Org2
3, Org3
4, Org4
];

I need to create a table where every possible unique observation in the Org_Name column in table 2 is associated with each employee in table one where the role is manager. In other words, I need a separate row per Manager per Org_Name like the following:

Role	ID	Org_Name
Manager	1153	Org1
Manager	1153	Org2
Manager	1153	Org3
Manager	1153	Org4

Can anyone advise me on the best way to go about that in Qlikview? I haven't had much luck with the loop syntax as yet.

Re: Combining Columns from Two Tables

Vegar — Thu, 14 Feb 2019 23:10:24 GMT

You are looking for a full outer join, also known as join in Qlikview.
Try the following script.

Table1:
LOAD * INLINE [
Role, ID
Manager, 1153
Employee, 1154
Employee, 1155
Employee, 1156
];

JOIN (Table1)
LOAD Org_Name
INLINE [
Org_CD, Org_Name
1, Org1
2, Org2
3, Org3
4, Org4
];

Re: Combining Columns from Two Tables

PCullinan — Thu, 14 Feb 2019 23:21:23 GMT

Wow, I can't believe I over complicated something so simple. Thanks for the quick response! I just needed to add a Where clause to restrict to only a single employee type. I'm posting my script below in case anyone else needs to see what the solution looked like using inline loads.

Table1:
LOAD
Role,
ID
Where Role = 'Manager';

LOAD * INLINE [
Role, ID
Manager, 1153
Employee, 1154
Employee, 1155
Employee, 1156
];

Join(Table1)

LOAD Org_Name
INLINE [
Org_CD, Org_Name
1, Org1
2, Org2
3, Org3
4, Org4
];