topic Table refering to itself in QlikView

Table refering to itself

Fri, 20 Jun 2014 14:47:33 GMT

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

Re: Table refering to itself

its_anandrjs — Fri, 20 Jun 2014 14:52:35 GMT

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$];

Re: Table refering to itself

ashfaq_haseeb — Fri, 20 Jun 2014 14:53:26 GMT

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

Re: Table refering to itself

alexandros17 — Fri, 20 Jun 2014 14:53:46 GMT

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

Re: Table refering to itself

Fri, 20 Jun 2014 15:12:34 GMT

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 ??

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

Re: Table refering to itself

its_anandrjs — Fri, 20 Jun 2014 15:24:19 GMT

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 :-

Re: Table refering to itself

Fri, 20 Jun 2014 15:34:31 GMT

I went with Hierarchy approach and it is working great 🙂

Thank you so much

Kiru