Joining two tables and returning only max value

[Anonymous]

Hi ... I need some help doing the following.

I am simply joining the two tables below on a left join on 'JOB_NO'. I want to return results based on the max 'VALUE' based on JOB_NO. Expected values posted at bottom.

A:

LOAD * INLINE

[

JOB_NO, BU, PERSON

A1, ROADS, TOM

B1, BRIDGE, BOB

C1, LIGHTING, ANDY

D1, LABOUR, HARRY

]

B:

LOAD * INLINE

[

SALES_NO, SALES_NAME, VALUE

A1, GREECE, 500

A1, GREECE, 1000

B1, FRANCE, 20

B1, FRANCE, 600

C1, SPAIN, 1000

C1, SPAIN, 500

D1,GERMANY,1000

D1,GERMANY, 20000

]

Expected result :

SALES_NO, SALES_NAME, VALUE, BU, PERSON

A1, GREECE, 1000, ROADS, TOM

B1, FRANCE, 600, BRIDGE, BOB

C1, SPAIN, 1000, LIGHTING, ANDY

D1,GERMANY, 20000, BU, PERSON

[sunny_talwar]

May be this

A:

LOAD JOB_NO as SALES_NO,

BU,

PERSON;

LOAD * INLINE [

    JOB_NO, BU, PERSON

    A1, ROADS, TOM

    B1, BRIDGE, BOB

    C1, LIGHTING, ANDY

    D1, LABOUR, HARRY

];

Left Join (A)

LOAD SALES_NO,

SALES_NAME,

Max(VALUE) as VALUE

Group By SALES_NO, SALES_NAME;

LOAD * INLINE [

    SALES_NO, SALES_NAME, VALUE

    A1, GREECE, 500

    A1, GREECE, 1000

    B1, FRANCE, 20

    B1, FRANCE, 600

    C1, SPAIN, 1000

    C1, SPAIN, 500

    D1, GERMANY, 1000

    D1, GERMANY, 20000

];

[maxgro]

RESULT

SCRIPT

A:

LOAD * INLINE

[

JOB_NO, BU, PERSON

A1, ROADS, TOM

B1, BRIDGE, BOB

C1, LIGHTING, ANDY

D1, LABOUR, HARRY

];

B:

LOAD * INLINE

[

SALES_NO, SALES_NAME, VALUE

A1, GREECE, 500

A1, GREECE, 1000

B1, FRANCE, 20

B1, FRANCE, 600

C1, SPAIN, 1000

C1, SPAIN, 500

D1,GERMANY,1000

D1,GERMANY, 20000

];

C:

NoConcatenate load *

Resident B

where SALES_NO <> Peek('SALES_NO')

Order By SALES_NO, VALUE desc;

DROP Table B;

Left Join (C) load JOB_NO as SALES_NO, BU, PERSON Resident A;

DROP Table A;

[Anonymous]

Im getting duplicate values returned with both solutions. I've attached my actual data. In theory it should only return 3 rows since there are 3 unique Job Nos.

Thanks in advance

[sunny_talwar]

Where is this data attached?

[Anonymous]

Apologies. Now attached.

[sunny_talwar · Accepted solution]

From Table1 tab, which CompanyCode and LogDate would you consider? This gives me a total of 2296 rows, but not sure how you are looking for only 3 rows when Company Code and LogDate are not unique per ContractCode.

A:

LOAD Category,

    CompanyCode,

    ContractCode,

    LogDate

FROM

[..\..\Downloads\Sample Data.xlsx]

(ooxml, embedded labels, table is Table1);

Left Join (A)

LOAD [Job No] as ContractCode,

    [Job Name],

    Max(Value) as Value

FROM

[..\..\Downloads\Sample Data.xlsx]

(ooxml, embedded labels, table is Table2)

Group By [Job No], [Job Name];

[Anonymous]

Yes, you're absolutely right. Ignore the statement about 3 rows.

Is there any way to get round this in order to return max value solely based on job no ?

[sunny_talwar]

So, you do want to see 2296 rows, right? The above is leading to 2296 rows with the Max(value)... have you looked at the attachment or tried the script?