Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Live chat with experts, bring your API Integration questions. June 15th, 10 AM ET. REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
roy_fredericks
Contributor
Contributor

Joining two tables and returning only max value

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

1 Solution

Accepted Solutions
sunny_talwar

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

View solution in original post

8 Replies
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
MVP
MVP



RESULT

1.png

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;

roy_fredericks
Contributor
Contributor
Author

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?

roy_fredericks
Contributor
Contributor
Author

Apologies. Now attached.

sunny_talwar

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

View solution in original post

roy_fredericks
Contributor
Contributor
Author

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?