Matching and multiplying in load script?

[TobiasTebben]

Dear community members,

I have one more newbie-question that I have tried to solve on my own for many hours...

Two tables:

OrderNo	Product	Quantity
1	A	2
2	A	3
3	B	2
4	C	4

Product	Price per Unit
A	3
B	4
C	5
D	6

All I want to do is to add to my first table the value of the order, i.e. Quantity x Price per Unit. I am trying to do this in my load script in order to have this information ready for easy analysis in my app.

Again, I highly appreciate your help!

Thanks!

Tobi

[rubenmarin]

hi, you can do it with a Join to have all fields in the same table or using a mapping table, like:

mapPrice:
Mapping LOAD Product, Price
From/Resident...; // load from table

OrderTable:
LOAD *, Quantity*Price as OrderValue;
LOAD OrderNo, Product, Quantity,
  Applymap('mapPrice', Product) as Price
From/resident... // load from table

[PrashantSangle]

Hi,

You can also use join. Like

Base:

Load * from table1;

Left Join

Load Product, [Price Per Unit] from table2;

NoConcatenate

Final:

Load *, Quantity * [Price Per Unit] as Sale_price Resident Base;

Drop table Base;

Regards,

Prashant Sangle

[Saravanan_Desingh]

One solution is.

tab1:
LOAD * INLINE [
    OrderNo, Product, Quantity
    1, A, 2
    2, A, 3
    3, B, 2
    4, C, 4
];
Left Join(tab1)
Price:
LOAD * INLINE [
    Product, Price per Unit
    A, 3
    B, 4
    C, 5
    D, 6
];

tab2:
LOAD OrderNo, Quantity*[Price per Unit] As Value
Resident tab1;

[Usama · Accepted solution]

Price_Mapping:
    Mapping load
         Product,
          PricePerUnit
From/Resident Table_Name;

OrderTable:
  LOAD *,
  Quantity * PricePerUnit as Value;

      LOAD
         OrderNo,
          Product,
          Quantity,
          Applymap('Price_Mapping', Product, 0) as PricePerUnit //if any of product have no value it will display 0.
From/Resident Table_Name;

[Kushal_Chawda]

go with what @rubenmarin  suggested  to avoid any join. But you can also use below to minimize load statements

mapPrice:
Mapping LOAD Product, Price
FROM Product; // load from table

OrderTable:
LOAD OrderNo,
Product,
Quantity,
Quantity * Applymap('mapPrice', Product,0) as Sales
FROM Order // load from table

[TobiasTebben]

Dear Usama,

thank you very much. The script does exactly what it‘s supposed to do. It generates (at least in the context of my app) a synthetic key. For me, this does not seem to be any problem. I just wanted to share this information in case someone else has a similar problem and finds this thread in the form.

Thanks again to all you great people who were so kind to answer my question!

Toni