Table refering to itself

[#1]

Hello Guys,

It would be very great if some one could tell me what's the best approach to refer a table to itself in data modeling to show some hierarchies in the user interface.

Actually,I have a table called Employee and it is referencing to itself using EmployeeBusinessID and ParentEmployeeBusinessID.

Thanks in advance!

Kiru

[its_anandrjs]

Use Hierarcy in the table load while load the table might will help you for creating the hierarchy see example for that from the help same you can use in your table load but change the field name.

Example:

Hierarchy(NodeID, ParentID, NodeName) LOAD

NodeID,

ParentID,

NodeName,

Attribute

FROM data.xls (biff, embedded labels, table is [Sheet1$];

[ashfaq_haseeb]

Hi,

Look at resident load and preceding load feature.

Look at the below posts.

http://community.qlik.com/blogs/qlikviewdesignblog/2013/03/04/preceding-load

Re: Resident Load

Hope it help.

Regards

ASHFAQ

[alexandros17]

If your table contains a hierarchy (father-son) you can use hierarchy functions (take a look in the guide),

otherwise split the list of data ointo 3 tables, repeating in each only the fields that link tables toghether

[#5]

Thank you all for your prompt reply.

Hi Anand,

Is there any way I can use this function directly while fetching data from ms access ??

or

can't I use a simple inner join to achieve hierarchy instead of any other functions (The same sql self join to refer a table to itself)

kindly let me know.

Thanks so much

Kiru

[its_anandrjs · Accepted solution]

Yes and there is another way of doing this as you say inner join the tables with the help of any resident table will also help you to get desire table by the help of joins and resident table. Or you can do directly in one of the table also if the table fields has proper data by referring to the fields for this see the example load and create another field with concatenate them and after loading this table on the front end plot the field Hierarchy. And the properties select show as tree view and with separator | .

Table:

LOAD DepId, Manager, EmployeeBusinessID, ParentEmployeeBusinessID,

          DepId & '|'& Manager & '|'& EmployeeBusinessID & '|'& ParentEmployeeBusinessID AS Hierarchy;

LOAD * Inline

[

DepId, Manager, EmployeeBusinessID, ParentEmployeeBusinessID

AXL,M1,EPM1,A2

axl,M1,EMP2,S3

CWV,M2,EMP3,A4

CWV,M2,EMP4,D5

PUR,M3,EMP5,G6

PUR,M3,EMP6,F3

];

And you get this type of field :-

[#7]

I went with Hierarchy approach and it is working great 🙂

Thank you so much

Kiru