indirect mapping invoice to order

[qlikhans]

Hi

my todays challenge:

3 tables

Table 1
Order Number	Qty
1510001	2
1510002	3
1510003	1
1510004	4
1510005	5
1510006	2
1510007	8
1510008	2
1510009	1

Table 2
Delivery Number	Predecessor	Predecessor Table
1520001	1510001	1
1520002	1510003	1
1520003	1510002	1
1520004
1520005	1510004	1
1520006
1520007	1510005	1
1520008	1510006	1

Table 3
Invoice Number	Predecessor	Predecessor Table
1530001	1520001	2
1530002	1520003	2
1530003	1520002	2
1530004	1520005	2
1530005	1510007	1
1530006	1510009	1

My Invoice table does not contain always an ordernumber. It contains the reference to the document that triggers the invoice and the reference from which table that trigger document originates. The trigger is either a delivery document, or the order document itself.

how to connect ordernumber to the invoicenumber. the result in the end needs to give me all orders and all invoices

thank you for your help

[swuehl]

Not sure if I fully understand what you are telling, could you maybe post the expected result and how you have derived the result using your sample records?

[jonathandienst]

Maybe load them like this and let QV handle the relationships:

Table1:

LOAD [Order Number],

  [Qty]

FROM Table1;

// Connect Predecessor to the Order

Table2:

LOAD [Delivery Number],

  [Predecessor] as [Order Number]

FROM Table2;

// Connect Predecessor to the Delivery

Table3:

LOAD [Invoice Number],

  [Predecessor] as [Delivery Number]

FROM Table3;

(Adapt this pseudo code to your exact requirements)

[johanlindell]

Here's a possible solution. Not sure it works in all cases...

[qlikhans]

Hi Jonathan,

I do not think that is the solution. The predecessor in in table 3, can be both order number and delivery number.

So i.e. defining [Predecessor] as [Delivery Number] would define also the order numbers in table 3 as delivery number.

knd regards

[qlikhans]

Hi Johan,

I think your solution is in a file that is not attached?

knrgrds

[johanlindell]

Try this then, I see the file.

https://community.qlik.com/servlet/JiveServlet/download/1048075-228081/temp.qvw

//Johan

[jonathandienst]

Is there any way to distinguish between order numbers and delivery numbers in Table 3?

[qlikhans]

yes, the value in column [predecessor table] is an identifier:

if [predecessor table] = 1, than Predecessor = Order number

if [predecessor table] = 2, than predecessor = Delivery number

[qlikhans]

Hi Johan,

I think it may be close to the solution. However, what is missing now are documents that have no link to an invoice yet.

krg

Hans