topic Re. :Self join in QlikView

Self join

prabhu0505 — Mon, 10 May 2010 09:48:04 GMT

How to achieve self join ?

If table fields are :

Emp.Id

Name

Mgr.ID

Salary

Here Mgr.ID is again Emp.ID.

Re. :Self join

martin59 — Mon, 10 May 2010 09:53:02 GMT

Hello,

You have to use the Hierarchy.

Like this :


HIERARCHY (Emp.Id, Mgr.ID, Name, Manager, Employe, Path, '/', Niveau)
LOAD *
RESIDENT Emp;

Martin

Re. :Self join

prabhu0505 — Mon, 10 May 2010 09:57:01 GMT

Thanks ro reply, Martin.

But can you explain me what is the use of Hierarchy over here?

Thanks

Prabhu

Re. :Re: Re. :Self join

martin59 — Mon, 10 May 2010 10:16:57 GMT

With Hierarchy, you'll have one table with all levels of the Hierarchy. So, you can see the manager of the selected employee. Datas are in the same table.

Look this (QlikView Help) :

The hierarchy keyword is a prefix to the load and select statements. It is used to transform an adjacent nodes table to an expanded nodes table. Hence, the input table must be an adjacent nodes table. The output of the hierarchy prefix is an expanded nodes table with all the needed additional fields.
Adjacent nodes tables are tables where each record corresponds to a node and has a field that contains a reference to the parent node. In such a table the node is stored on one record only but can still have any number of children. The table may of course contain additional fields describing attributes for the nodes.
In expanded nodes tables each level in the hierarchy is stored in a separate field. The levels in an expanded nodes table can easily be used e.g. in a pivot table or a in a tree structure. More details about adjacent nodes tables and expanded nodes tables can be found in the Reference Manuals.
Normally the input table has exactly one record per node and in such a case the output table will contain the same number of records. However, sometimes there are nodes with multiple parents, i.e. one node is represented by several records. If so, the output table may have more records than the input table.
All nodes without a parent id or with a parent id not found in the nodeID column will be considered as roots. Also, only nodes with a connection to a root node - direct or indirect - will be loaded, thus avoiding circular references.
Additional fields containing the name of the parent node, the path of the node and the depth of the node can be created.

Martin

Re. :Self join

Mon, 10 May 2010 10:33:47 GMT

K:
LOAD * INLINE [
EmployeeId, Name, MgrID, Salary
1, Anthony, 5, 8000
2, Peter, 5, 8500
3, Keanu, 5, 10000
4, William, 5, 20000
5, Jessy, 5, 50000
];
P:

LOAD * INLINE [
EmployeeId, Name, MgrID, Salary
1, Anthony, 5, 8000
2, Peter, 5, 8500
3, Keanu, 5, 10000
4, William, 5, 20000
5, Jessy, 5, 50000
];

it will automatically join the data, since all the fields have same name

Re. :Re: Re. :Self join

martin59 — Mon, 10 May 2010 10:53:49 GMT

Look this application

Hop it's gonna be useful

Martin

Self join

johnw — Mon, 10 May 2010 21:40:42 GMT

Perhaps this:

LEFT JOIN (YourTable)
LOAD
Emp.Id as Mgr.ID
,Name as "Manager Name"
RESIDENT YourTable
;

Self join

prabhu0505 — Wed, 12 May 2010 06:28:28 GMT

Thanks for reply

John, this is really an useful tip.

Martin, thnx