Using column in other table in script

[zahidrahim_ocp]

Dear Experts,

I have two tables which i want to join and use one column in the second table like below:

ORDERS:

OrdId

ItemId

Quantity

Amount

PRICES:

ItemId

Price

What i want

ORDERS:

OrdId

ItemId

Quantity*Price

Amount

PRICES:

Outer Join(Orders)

ItemId

Price

Regards,

Zahid Rahim

[shraddha_g]

Orders:

Load

           OrdId

            ItemId

            Quantity

            Amount

From OrderTable;

Left join (Orders)

PRICES:

Load

             ItemId

             Price

From PricesTable;

noconcatenate

Final:

Load

        OrdId

        ItemId

       Quantity*Price

        Amount

Resident Orders;

Drop table Orders;

[antoniotiman]

Hi Zahid,

may be this

PRICES:
LOAD * Inline [
ItemId,Price
A,5
B,8];

ORDERS:
LOAD *,Lookup('Price','ItemId',ItemId,'PRICES')*Quantity as Value Inline [
OrdId,ItemId,Quantity,Amount
1,A,10,20
1,B,20,20
2,A,15,10
2,B,25,10];

Regards,

Antonio

[shraddha_g]

Also you can try,

Map_Price:

Mapping Load

             ItemId,

             Price

From PricesTable;

Orders:

Load

           OrdId,

            ItemId,

           Quantity,

            Quantity * Applymap('Map_Price',ItemId) as Newfield,

            Amount

From OrderTable;

[antoniotiman]

Yes,

however You lose table PRICES in Data Model.

[shraddha_g]

Yes..

If you are getting Price field in Order table then you don't need it. Unless same table is used for another purpose.