topic Re: How to find max value in QlikView

How to find max value

Thu, 12 Sep 2013 19:10:48 GMT

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;

Re: How to find max value

jpapador — Thu, 12 Sep 2013 19:21:44 GMT

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

Re: How to find max value

Gysbert_Wassenaar — Thu, 12 Sep 2013 19:29:27 GMT

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.

Re: How to find max value

Thu, 12 Sep 2013 19:56:54 GMT

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

Re: How to find max value

Peter_Cammaert — Mon, 16 Sep 2013 20:01:03 GMT

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

Re: How to find max value

Clever_Anjos — Mon, 16 Sep 2013 20:06:53 GMT

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;