Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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];
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
];
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;
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
Where is this data attached?
Apologies. Now attached.
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];
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 ?
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?