Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
qlikhans
Contributor

indirect mapping invoice to order

Hi

my todays challenge:

3 tables

 

Table 1
Order NumberQty
15100012
15100023
15100031
15100044
15100055
15100062
15100078
15100082
15100091

  

Table 2
Delivery NumberPredecessorPredecessor Table
152000115100011
152000215100031
152000315100021
1520004
152000515100041
1520006
152000715100051
15200081510006

1

 

Table 3
Invoice NumberPredecessorPredecessor Table
153000115200012
153000215200032
153000315200022
153000415200052
153000515100071
153000615100091

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

1 Solution

Accepted Solutions
qlikhans
Contributor

Re: indirect mapping invoice to order

Hi Johan,

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

t.u.

krgds

Hans !

11 Replies
MVP
MVP

Re: indirect mapping invoice to order

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?

MVP
MVP

Re: indirect mapping invoice to order

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)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
johanlindell
Contributor II

Re: indirect mapping invoice to order

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

qlikhans
Contributor

Re: indirect mapping invoice to order

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
Contributor

Re: indirect mapping invoice to order

Hi Johan,

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

knrgrds

johanlindell
Contributor II

Re: indirect mapping invoice to order

MVP
MVP

Re: indirect mapping invoice to order

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
qlikhans
Contributor

Re: indirect mapping invoice to order

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
Contributor

Re: indirect mapping invoice to order

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

Community Browser