Link the column with other table

[Anonymous]

Hi ,

    I have two tables. One is transaction and other one is Price master

Transaction:  

LOCATION	ITEM CODE	DATE	QUANTITY
Chennai	A001	05-Jan-18	4
Chennai	A001	07-Feb-18	2
Chennai	B001	06-Feb-18	2
Chennai	B001	05-Jan-18	3
Bangalore	B001	05-Feb-18	1
Bangalore	B001	06-Feb-18	3
Bangalore	C001	08-Jan-18	2
Bangalore	C001	09-Feb-18	5
Mumbai	A001	05-Jan-18	2
Mumbai	A001	07-Feb-18	1
Mumbai	C001	07-Jan-18	2
Mumbai	C001	08-Feb-18	3

Price Master:

LOCATION	ITEM CODE	FROM DATE	TO DATE	PRICE
Chennai		31-Jan-18	100	100
Chennai		28-Feb-18	110	110
	C001	31-Jan-18	210	210
	C001	28-Feb-18	200	200
	A001	31-Jan-18	110	110
	A001	28-Feb-18	115	115
	B001	31-Jan-18	150	150
	B001	28-Feb-18	200	200

I need to link the Price column which is in price master with Transaction column based on location and Item Code and also from date to To date.

The Sample output should be like this.

LOCATION	ITEM CODE	DATE	QUANTITY	PRICE
Chennai	A001	05-Jan-18	4	100
Chennai	A001	07-Feb-18	2	110
Chennai	B001	06-Feb-18	2	200
Chennai	B001	05-Jan-18	3	150
Bangalore	B001	05-Feb-18	1	200
Bangalore	B001	06-Feb-18	3	200
Bangalore	C001	08-Jan-18	2	210
Bangalore	C001	09-Feb-18	5	200
Mumbai	A001	05-Jan-18	2	110
Mumbai	A001	07-Feb-18	1	115
Mumbai	C001	07-Jan-18	2	210
Mumbai	C001	08-Feb-18	3	200

[agomes1971]

Hi,

have you tried this:

Trans:

LOAD LOCATION,

     [ITEM CODE],

     DATE,

     QUANTITY

FROM

[Data.xls]

(biff, embedded labels, table is Transaction$);

Left Join

//NoConcatenate

Master_Price:

LOAD [ITEM CODE],

     PRICE

FROM

[Data.xls]

(biff, embedded labels, table is [Price Master$]);

HTH

André Gomes