Data mapping condition in script

[#1]

Lets say I have two tables looks like below

Table 1

ID	Product	Amount
1	A	100
1	B	200

Table2

ID	Product	Amount
1	A	300
1	C	250

Now I want to create the Final table with below condition

If ID & Product from Table1 exist in Table2 , then Take the Amount wherever it is greater, in this case it will pick from table2.

Also take ID & Product from Table2 which not there in Table1.

Final Output

ID	Product	Amount
1	A	300
1	B	200
1	C	250

[#2]

This is also slow down the reload task and consuming memory

[#3]

still the same issue

[sunny_talwar]

Do you know which part (concatenation or the resident) is slowing down the reloading? and have you made comparison as to which method has done the best so far. May be we can choose one of the methods (most likely the best performing one) and look for ways to optimize it further

[#5]

issue is ate resident load

[sunny_talwar]

Does it take substantially lesser time if you remove the where clause?

FinalTable:

NoConcatenate

LOAD ID,

  Product,

  Amount

Resident Table

Where ID&Product <> Previous(ID&Product)

Order By ID, Product, Amount desc;

DROP Table Table;

[Kushal_Chawda · Accepted solution]

Give it a try

Map:

mapping LOAD

    Autonumber(ID&Product) as Key,

    Amount

FROM Table2;

Data1:

LOAD ID,

    Product,

    Autonumber(ID&Product) as Key,

    rangemax(applymap('Map',Autonumber(ID&Product)),Amount) as Amount

FROM Table1;

concatenate(Data1)

LOAD *

where not exists(Key);

LOAD ID,

    Product,

    Amount,

    Autonumber(ID&Product) as Key

FROM Table2;

Drop Field Key;

[#8]

Yes without where it works good and even better without order by

[#9]

swuehl‌ can you suggest something?

[#10]

@stalwar1  any update.

[#11]

This looks ok