Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
LINKEDIN LIVE: Democratizing data to enhance customer-centricity. JULY 29TH 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?