2 Replies Latest reply: May 29, 2015 2:57 AM by Jonathan Dienst RSS

    maximum value require

    Anil kumar

      Hi All ,

       

      //AR_CASH_RECEIPT_HISTORY_ALL:

      //LOAD CASH_RECEIPT_ID,

      //CASH_RECEIPT_HISTORY_ID,

      //RECEIPT_STATUS

      //FROM

      //[C:\Qlikview\Anil\Stage 1\QVD\AR_CASH_RECEIPT_HISTORY_ALL.qvd]

      //(qvd);

       

      from this script i needed maximum value against CASH_RECEIPT_HISTORY_ID against CASH_RECEIPT_ID so i  did this script -

      TAB1:

      LOAD CASH_RECEIPT_HISTORY_ID,

      CASH_RECEIPT_ID,

      RECEIPT_STATUS

      FROM

      [C:\Qlikview\Anil\Stage 1\QVD\AR_CASH_RECEIPT_HISTORY_ALL.qvd]

      (qvd);

       

       

      NOCONCATENATE

      TAB2:

      LOAD CASH_RECEIPT_ID,

      MAX(CASH_RECEIPT_HISTORY_ID) AS CASH_RECEIPT_HISTORY_ID_MAX

      FROM

      [C:\Qlikview\Anil\Stage 1\QVD\AR_CASH_RECEIPT_HISTORY_ALL.qvd]

      (qvd)

      Group by CASH_RECEIPT_ID;

       

       

      NoConcatenate

      AR_CASH_RECEIPT_HISTORY_ALL:

      LOAD *

      RESIDENT TAB1;

      DROP TABLE TAB1;

      LEFT JOIN(AR_CASH_RECEIPT_HISTORY_ALL)

      LOAD *

      RESIDENT TAB2;

      DROP TABLE TAB2;

       

       

      this script working correct but still i need maximum  correct CASH_RECEIPT_ID against CASH_RECEIPT_HISTORY_ID . plz suggest what query i need to put her

      /// i need maximum value of CASH_RECEIPT_HISTORY_ID against CASH_RECEIPT_ID with correct RECEIPT_STATUS also-

        • Re: maximum value require
          Andrey Fomichev

          TAB1:

          LOAD CASH_RECEIPT_HISTORY_ID,

          CASH_RECEIPT_ID,

          RECEIPT_STATUS

          FROM

          [C:\Qlikview\Anil\Stage 1\QVD\AR_CASH_RECEIPT_HISTORY_ALL.qvd]

          (qvd);

           

          TAB2:

          LOAD CASH_RECEIPT_ID,

          MAX(CASH_RECEIPT_HISTORY_ID) AS CASH_RECEIPT_HISTORY_ID_MAX

          Resident TAB1

          WHERE RECEIPT_STATUS='Correct'

          Group by CASH_RECEIPT_ID;

           

          AR_CASH_RECEIPT_HISTORY_ALL:

          LOAD *

          RESIDENT TAB1;

          LEFT JOIN(AR_CASH_RECEIPT_HISTORY_ALL)

          LOAD *

          RESIDENT TAB2;

          DROP TABLE TAB1;

          DROP TABLE TAB2;

          • Re: maximum value require
            Jonathan Dienst

            Is this what you need?

             

            TAB1:

            LOAD CASH_RECEIPT_HISTORY_ID,

            CASH_RECEIPT_ID,

            RECEIPT_STATUS

            FROM [C:\Qlikview\Anil\Stage 1\QVD\AR_CASH_RECEIPT_HISTORY_ALL.qvd] (qvd);

             

            LEFT JOIN(TAB1)

            LOAD CASH_RECEIPT_ID,

            MAX(CASH_RECEIPT_HISTORY_ID) AS CASH_RECEIPT_HISTORY_ID_MAX

            FROM [C:\Qlikview\Anil\Stage 1\QVD\AR_CASH_RECEIPT_HISTORY_ALL.qvd] (qvd)

            Group by CASH_RECEIPT_ID;

             

            AR_CASH_RECEIPT_HISTORY_ALL:

            LOAD RECEIPT_HISTORY_ID,

              CASH_RECEIPT_ID,

              RECEIPT_STATUS

              If(CASH_RECEIPT_HISTORY_ID = CASH_RECEIPT_HISTORY_ID_MAX, 1, 0) As ISMAX_RECEIPT_HISTORY

            Resident TAB1;

             

            DROP TABLE TAB1;