Problem with self-referencing table

[qw_johan]

I have a problem with a self-referencing table. This is my first time working with it. I have included an example below and attached a file. I am really struggling with this.

The lower table is self-referencing

Some contract numbers have a reference to a parent contract (reference no). And a few of those contract numbers has a second parent contract (reference no). It is like a hierarchy of contracts you could say. Unfortunately, the parent contract doesn’t have a direct connection to a customer, only through a reference no.

For example customer no 22 has contract no 21. No problem this far.
Contract no 21 have a reference to another contract, number 588.
Contract no 588 references contract number 527.
Contract 527 is a multi-level SLA with no parent.

How can I in my example create a key between customer 22 and contract numbers 588 and 527 ?

What I would like to achieve is either a table with one row per customer or a table that shows customer and all contract numbers linked to that customer.  

Like I said, I am really struggling with this. So, any help is appreciated. 🙂

Thanks

[Marcos_Ferreira_dos_Santos]

Hi Qw_johan,

Have you solved this question by yourself ?

If you don't, I may give a suggestion: create a improved temporary table for your .QVW and fill it in script time:

Customer;Contract;Level

22;21;1

22;588;2

22;527;3

With that you will easily achieve what you need, won't you ?

Good luck!

[qw_johan]

Hi Marcos

No I have not yet solved it. So any help is still very much appreciated.

How would I create this improved temp table from the two existing tables in script?
I can't seem to figure out how to script a relation between 22->588 and 22->527.🤔
I know there is a relationship between 21->588 in the second table (and also 588->527), I should be able to use that connection somehow.
But so far I haven't been able to create this in script. 

//Johan

[qw_johan · Accepted solution]

I think I figured it out. Here is my solution.

t1:
NoConcatenate LOAD Customer, [Contract No] Resident CUSTOMERS;
LEFT JOIN (t1) LOAD [Contract No], [Contract No] as [Contract No 1], [Reference No] as [Reference No 1] Resident CONTRACTS;

t2:
NoConcatenate LOAD Customer, [Reference No 1] as [Contract No], [Contract No 1], [Reference No 1] Resident t1;
LEFT JOIN (t2) LOAD [Contract No], [Reference No] as [Reference No 2] Resident CONTRACTS;

t3:
NoConcatenate
LOAD Customer, if(not IsNull([Reference No 2]), [Contract No 1]&'_'&[Reference No 1]&'_'&[Reference No 2],
if(not IsNull([Reference No 1]), [Contract No 1]&'_'&[Reference No 1],
If(not IsNull([Contract No 1]),[Contract No 1]))) as [Contract No]
Resident t2
;

drop tables CUSTOMERS, CONTRACTS, t1, t2;

This will end up with the following table.