5 Replies Latest reply: Sep 16, 2013 4:06 PM by Clever Anjos RSS

    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;

        • Re: How to find max value
          Jared Papador

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

          • Re: How to find max value
            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.

            • Re: How to find max value
              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

              • Re: How to find max value
                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
                ;