topic Re: Joining two tables and returning only max value in QlikView

Joining two tables and returning only max value

Anonymous — Thu, 08 Feb 2018 12:13:12 GMT

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.

LOAD * INLINE

[

JOB_NO, BU, PERSON

A1, ROADS, TOM

B1, BRIDGE, BOB

C1, LIGHTING, ANDY

D1, LABOUR, HARRY

]

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

Re: Joining two tables and returning only max value

sunny_talwar — Thu, 08 Feb 2018 12:23:16 GMT

May be this

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

];

Re: Joining two tables and returning only max value

maxgro — Thu, 08 Feb 2018 12:25:59 GMT

RESULT

SCRIPT

LOAD * INLINE

[

JOB_NO, BU, PERSON

A1, ROADS, TOM

B1, BRIDGE, BOB

C1, LIGHTING, ANDY

D1, LABOUR, HARRY

];

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

];

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;

Re: Joining two tables and returning only max value

Anonymous — Thu, 08 Feb 2018 14:21:23 GMT

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

Re: Joining two tables and returning only max value

sunny_talwar — Thu, 08 Feb 2018 14:27:43 GMT

Where is this data attached?

Re: Joining two tables and returning only max value

Anonymous — Thu, 08 Feb 2018 14:29:11 GMT

Apologies. Now attached.

Re: Joining two tables and returning only max value

sunny_talwar — Thu, 08 Feb 2018 14:38:57 GMT

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.

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];

Re: Joining two tables and returning only max value

Anonymous — Thu, 08 Feb 2018 15:13:52 GMT

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 ?

Re: Joining two tables and returning only max value

sunny_talwar — Thu, 08 Feb 2018 15:17:06 GMT

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?