topic Re: indirect mapping invoice to order in QlikView

indirect mapping invoice to order

qlikhans — Wed, 25 May 2016 14:57:25 GMT

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

Re: indirect mapping invoice to order

swuehl — Wed, 25 May 2016 15:41:20 GMT

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?

Re: indirect mapping invoice to order

jonathandienst — Thu, 26 May 2016 08:29:29 GMT

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)

Re: indirect mapping invoice to order

johanlindell — Thu, 26 May 2016 09:01:18 GMT

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

Re: indirect mapping invoice to order

qlikhans — Thu, 26 May 2016 09:05:00 GMT

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

Re: indirect mapping invoice to order

qlikhans — Thu, 26 May 2016 09:08:39 GMT

Hi Johan,

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

knrgrds

Re: indirect mapping invoice to order

johanlindell — Thu, 26 May 2016 09:47:07 GMT

Try this then, I see the file.

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

//Johan

Re: indirect mapping invoice to order

jonathandienst — Thu, 26 May 2016 11:21:10 GMT

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

Re: indirect mapping invoice to order

qlikhans — Thu, 26 May 2016 11:40:56 GMT

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

Re: indirect mapping invoice to order

qlikhans — Thu, 26 May 2016 12:16:26 GMT

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

Re: indirect mapping invoice to order

johanlindell — Thu, 26 May 2016 12:29:03 GMT

Hi Hans,

Not sure what you mean. All orders and deliveries and invoices are there. If there is no invoice, there is no invoice.

/Johan

Re: indirect mapping invoice to order

qlikhans — Thu, 26 May 2016 13:12:50 GMT

Hi Johan,

You are correct, I did not look in the correct place. This is a solution that works.

t.u.

krgds

Hans !