Join on basis of Minimum value of feild

[sujit_nath]

I have a base table, suppose TableA with headers

Key1

Key2

I want to add Description field from from TableB, which is as:

Key1	Key2	Description	Priority
A	B	desc1	1
A	B	desc2	2
A	B	desc3	3
C	D	desc4	3
C	D	desc5	2
E	F	desc6	2
E	F	desc7	1
E	F	desc8	5

-The scenario is as, I want to join tableB to tableA on feilds Key1 and Key2 and add Description to table A, only the description whose priority is least should be added. (The rows in red)

How do I achieve this?

[YoussefBelloum]

Hi,

what do you mean "when the priority is least.." ?

[sunny_talwar]

Try this

Table:

LOAD * INLINE [

    Key1, Key2

    A, B

    C, D

    E, F

];

Left Join (Table)

LOAD Key1,

Key2,

FirstSortedValue(Description, Priority) as Description

Group By Key1, Key2;

LOAD * INLINE [

    Key1, Key2, Description, Priority

    A, B, desc1, 1

    A, B, desc2, 2

    A, B, desc3, 3

    C, D, desc4, 3

    C, D, desc5, 2

    E, F, desc6, 2

    E, F, desc7, 1

    E, F, desc8, 5

];

[dapostolopoylos]

I 've got a solution similar to the previous posted, check it out:

B:

LOAD

     *,

     Key1&Key2&Priority as ComboKey;

LOAD * INLINE [

    Key1, Key2, Description, Priority

    A, B, desc1, 1

    A, B, desc2, 2

    A, B, desc3, 3

    C, D, desc4, 3

    C, D, desc5, 2

    E, F, desc6, 2

    E, F, desc7, 1

    E, F, desc8, 5

];

inner Join (B)

B_tmp:

LOAD

     Key1&Key2&Priority as ComboKey;

LOAD

     Key1,

     Key2,

     Min(Priority) as Priority

Resident

     B

Group By

     Key1,

     Key2;

DROP Field ComboKey;

[sujit_nath]

Least value. Among 1,2 and 3, the priority 1 should take over.

[sunny_talwar]

Did you get to try the two other responses you got below?

[sujit_nath]

hi Sunny, I'm trying your response and that should solve my purpose.

Would let you know after I test it on the complete data set.

Thanks a lot lot your help!

[sunny_talwar]

Sounds good