Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi Johan,
You are correct, I did not look in the correct place. This is a solution that works.
t.u.
krgds
Hans !
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?
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)
Here's a possible solution. Not sure it works in all cases...
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
Hi Johan,
I think your solution is in a file that is not attached?
knrgrds
Try this then, I see the file.
https://community.qlik.com/servlet/JiveServlet/download/1048075-228081/temp.qvw
//Johan
Is there any way to distinguish between order numbers and delivery numbers in Table 3?
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
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