How to find max value

[#1]

Dear QV Friends

I have following data

order_id     cert_id     cert_status     name

987          123               C              abcd

987          124               L               abcd

987          125               A               abcd

I wish to pick cert_status, name and order_id corresponding to maximum serial no of cert_id, i.e. the highlighted one.

I'm using below and getting 3 rows. Please help. Thank You Much in advance.

CERT:

LOAD
ORDER_ID,
CERT_STATUS,

NAME,
MAX(CERTIFICATE_ID) as CERTIFICATE_ID
FROM
ABI_Qlikview\QVDS\ABI_BOOKING_CERT_F.qvd (qvd)
///where MATCH (ORDER_ID,10238495)
Group by ORDER_ID, CERT_STATUS,NAME;

[jpapador]

Is Certificate_ID stored as a number?  Try Max(Num(CERTIFICATE_ID)) as CERTIFICATE_ID

[Gysbert_Wassenaar]

If you want to load only the record with the maximum certificate id then try:

Temp:

Load MAX(CERTIFICATE_ID) as CMAXCERTIFICATE_ID

From ABI_Qlikview\QVDS\ABI_BOOKING_CERT_F.qvd (qvd);

LET vMaxCert = peek('CMAXCERTIFICATE_ID');

Drop table Temp;

CERT:

LOAD
ORDER_ID,
CERT_STATUS,

NAME,
CERTIFICATE_ID
FROM
ABI_Qlikview\QVDS\ABI_BOOKING_CERT_F.qvd (qvd)
where CERTIFICATE_ID = $(vMaxCert);

If you want to load all records and use expression in a chart you can use the firstsortedvalue function. For example firstsortedvalue(ORDER_ID, -CERTIFICATE_ID) to find the order id of the maximum certificate id.

[#4]

Thanks Wassenaar for your reply. When I execute the script, TEMp table is fetching correct data but CERT table is fetching 0 records. CAn you Please see.

Thanks

[Peter_Cammaert]

Even simpler, if cert_id is unique across all records:

CERT:

LOAD max(cert_id) as cert_id

FROM [ABI_Qlikview\QVDS\ABI_BOOKING_CERT_F.qvd] (qvd)

LEFT JOIN(CERT)
LOAD cert_id, order_id, cert_status, name
FROM [ABI_Qlikview\QVDS\ABI_BOOKING_CERT_F.qvd] (qvd)

Is that usable ?

Peter

[Clever_Anjos]

CERT:

LOAD
ORDER_ID,
firstsortedvalue(CERT_STATUS,-CERTIFICATE_ID) as cert_status

firstsortedvalue(NAME,-CERTIFICATE_ID) as name,
MAX(CERTIFICATE_ID) as CERTIFICATE_ID
FROM
ABI_Qlikview\QVDS\ABI_BOOKING_CERT_F.qvd (qvd)
///where MATCH (ORDER_ID,10238495)
Group by ORDER_ID;