Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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