Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
Is Certificate_ID stored as a number? Try Max(Num(CERTIFICATE_ID)) as CERTIFICATE_ID
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.
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
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
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;