Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

How to find max value

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;

5 Replies
jpapador
Not applicable

Re: How to find max value

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

Gysbert_Wassenaar
Not applicable

Re: How to find max value

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.


talk is cheap, supply exceeds demand
Not applicable

Re: How to find max value


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
Not applicable

Re: How to find max value

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
Not applicable

Re: How to find max value

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
;