topic Re: Problem with Max Function in QlikView

Problem with Max Function

Anonymous — Wed, 12 Aug 2015 14:43:48 GMT

Hello Community. I have a problem with the max function that I hope you can help me with:

I have the following data:

Seller	IDSeller	ContractID	Department	Year
John	1	1	A	2010
Tom	2	2	B	2011
Joe	3	3	C	2012
Sam	4	4	D	2013
John	1	5	E	2014

What I want to do, is only show the most recent data for each Seller, this means that the Seller John shuld only appear once with the data from the most recent year (2014). So the data should look like this:

Seller	IDSeller	ContractID	Department	Year
Tom	2	2	B	2011
Joe	3	3	C	2012
Sam	4	4	D	2013
John	1	5	E	2014

Hope you can help me

Re: Problem with Max Function

Clever_Anjos — Wed, 12 Aug 2015 15:36:31 GMT

Use

FirstSortedValue(Department,-Year)

FirstSortedValue(ContractID,-Year)

Re: Problem with Max Function

maximiliano_vel — Wed, 12 Aug 2015 16:21:28 GMT

LOAD

Seller,

IDSeller,

FirstSortedValue(ContractID, -Year) as ContractID,

FirstSortedValue(Department, -Year) as Department,

FirstSortedValue(Year, -Year) as Year

FROM [Contracts.xlsx]

(ooxml, embedded labels, table is Sheet1)

GROUP BY Seller, IDSeller;

Re: Problem with Max Function

jagan — Wed, 12 Aug 2015 16:30:17 GMT

Hi,

Try like this

Data:

LOAD

FROM DataSource;

LEFT JOIN(Data)

LOAD

IDSeller,

Max(ContractID) AS ContractID,

1 AS LatestFlag

RESIDENT Data;

Now in chart try like this

Chart : Straight Table

Dimension:Seller,IDSeller,ContractID,Department

Expression: Only({<LatestFlag={1}>} Year)

Hope this helps you.

Regards,

jagan.

Re: Problem with Max Function

sasiparupudi1 — Wed, 12 Aug 2015 19:08:49 GMT

Please check the attached file. hope this helps

Sasi