Skip to main content
Announcements
Marching toward a simplified navigation! READ ON
cancel
Showing results for 
Search instead for 
Did you mean: 
michael_luettge
Contributor
Contributor

Combine 2 tables

Hello all,

I have to combine the fields of 2 files. One of them has the invoice positions included and the other the historic article changes.

In the historical article changes are stored the Material prices of the invoiced articles. The goal is to link the articles in the invoice positions to the valid Material price defined with the article no. and the "valid from" date.

As first I have try to use the "LEFT JOIN" funktion but that doesn't work because I don't have 2 equal key fields.

The relation of the both filed should in my opnion

"Invoice position.Articleno" = " historic article changes.Articleno" and

"Invoice position.invoice date" >= "historic article changes.Valid from" and

only the first date of the "historic article changes.Valid from" that is less or equal have to be taken.


Have anybody an idea how I can solve this problem?

Thanks in advance!

Example:

Invoice position                                                                           historic article changes

Invoice no, Articleno, invoice date, invoice qty                            Articleno, Material price, Valid from

1234               A          01.03.18           5                                            A               5,00            15.02.18

1234               B          15.03.18           2                                            B               4,00            01.03.18

5678               A          01.04.18           8                                             A               6,50           10.03.18

5678               B          05.04.18          7                                             B               5,50            01.04.18

I would like to have the following sollution:

(the sort of the file doesn't matter, I have sort thew only for the example)

Invoice position

Invoice no, Articleno, invoice date, invoice qty, Material price, Valid from

1234               A          01.03.18           5               5,00            15.02.18

5678               A          01.04.18           8                6,50           10.03.18

1234               B          15.03.18           2               4,00            01.03.18

5678               B          05.04.18           7               5,50            01.04.18

0 Replies