Pick the data based on the max value.

[#1]

I have 2 tables.

Table 1:

ID    Amount    Unit

1      100          Office

1      200          Rent

1      300          Warehouse

2      20            Office

2      80            Warehouse

2      100          Rent

Table 2:

ID    Unit

1      Office

1      Warehouse

1      Rent

2      Office

2      Warehouse

2      Rent

Here I have to pick the Unit for ID based the Max amount

My desired output should look like below and also I need Unit as a filter..so they can filter the ID's based on the unit.

ID    Amount    Unit

1      300          Warehouse

2      100          Rent

Thanks much.

[sunny_talwar · Accepted solution]

May be this

Table1:

LOAD ID,

  Max(Amount) as Amount,

  FirstSortedValue(Unit, -Amount) as Unit

Group By ID;

LOAD * INLINE [

    ID,    Amount,    Unit

    1,      100,      Office

    1,      200,      Rent

    1,      300,      Warehouse

    2,      20,       Office

    2,      80,       Warehouse

    2,      100,      Rent

];

Left Join(Table1)

LOAD * INLINE [

    ID, Unit

    1, Office

    1, WareHouse

    1, Rent

    2, Office

    2, WareHouse

    2, Rent

];

[#3]

Sorry Sunny.My bad.I dont have Unit in Table 1.

[sunny_talwar]

So how else would do you know that 300 is associated with Unit Warehouse for ID = 1?

[#5]

based on the ID from Table 2.

[sunny_talwar]

Both tables have three IDs, how do I know ID 1 where Amount = 300 is associated Warehouse

[#7]

From Table 1 pick the max amount for each ID and then associate that ID to the Unit in Table2.

[#8]

I understood your question now..Let me write up the sample again..Thank you.

[sunny_talwar]

But your table 2 have all three Units

[sunny_talwar]

Sounds good