topic Re: How to find References of table entries recursively ? in QlikView

How to find References of table entries recursively ?

Fri, 11 Sep 2015 12:50:21 GMT

Hello Experts,

I have a table (a flat file with data) PUID as shown below

ABCD

EFGH

HIJK

JKLM

LMNO

OPQR

QRST

And I have another file PUIDREF which has entries like below.

ABCD	LMNO
EFGH	OPQR
HIJK	OPQR
LMNO	WXYZ

I am new to Qlikview and would like to solve this issue for one of my site consolidation tasks. I want find the references of entries from PUID in PUIDREF like below.

ABCD ~ LMNO

ABCD ~ WXYZ (This is because, ABCD is referencing LMNO and LMNO inturn WXYZ)

EFGH ~ OPQR

EFGH ~ HIJK

HIJK ~ OPQR

HIJK ~ EFGH

LMNO ~ WXYZ

Basically entries from PUID are searched in PUIDREF. this search continues in PUIDREF also, till there are no further references are found. How can i achieve this in QlikView ?

Re: How to find References of table entries recursively ?

Gysbert_Wassenaar — Sat, 12 Sep 2015 11:16:28 GMT

Try the Hierarchy function. See this blog post for more information: Unbalanced, n-level hierarchies

Re: How to find References of table entries recursively ?

Mon, 14 Sep 2015 08:48:30 GMT

Hi Gysbert,

Thank you for the post. As I explained, i am new to QlikView and not so good about QlikView syntax.

I have gone through the posts about n-level hierarchies but not getting a clue on where to start. I would appreciate if you could help me write the script ?

Re: How to find References of table entries recursively ?

Mon, 14 Sep 2015 09:06:14 GMT

Also I would like to ask i have to analyse this data over 3.5 billion records. Will QlikView be able to handle this much data with Hierarchy function, because, it increases the size of rows with the explanation given in the blogs by Heric.

Re: How to find References of table entries recursively ?

Gysbert_Wassenaar — Mon, 14 Sep 2015 10:37:58 GMT

It looks like you want to do a network graph analysis. The hierarchy function can't help solve that completely. Perhaps another tool will suit that purpose better.

Qlikview can handle 3.5 billion rows given enough ram and cpu resources. But perhaps your server can't.

Re: How to find References of table entries recursively ?

Mon, 14 Sep 2015 11:40:45 GMT

I will try to explain pictorially. My feel is that QlikView can solve, but i am not able to explain / explore properly.

If you see the above picture, i have two files. I am trying to figure all references of elements from file 1. What i mean is, i take ABCD, and search in file 2. i get, LMNO, Now LMNO is refereing WXYZ internally, and WXYZ is internally refering EFGH. I want to list till there is no further reference is found.

ABCD -> LMNO -> WXYZ -> EFGH.

Could you please help, if Qlikview can solve this ?

Re: How to find References of table entries recursively ?

Gysbert_Wassenaar — Tue, 15 Sep 2015 08:05:08 GMT

ABCD -> LMNO -> WXYZ -> EFGH.

That can be done with the Hierarchy function.

But getting from

EFGH OPQR

HIJK OPQR

To EFGH ~ HIJK and HIJK ~ EFGH is not possible with the Hierarchy function. I don't know of a good way to resolve that relationship.

Re: How to find References of table entries recursively ?

Tue, 15 Sep 2015 08:59:30 GMT

For each record from file 1, i would be searching all references like ABCD -> LMNO -> WXYZ -> EFGH in file 2.

But i am not interested to move from EFGH to HIJK because they are not referenced right.. I would like

EFGH -> WXYZ -> LMNO -> ABCD.

If you say moving from ABCD -> LMNO -> WXYZ -> EFGH is possible, could you please send me the Script of Hierarchy implementation ?