maximum value require

[yadav_anil782]

Hi All ,

//AR_CASH_RECEIPT_HISTORY_ALL:

//LOAD CASH_RECEIPT_ID,

//CASH_RECEIPT_HISTORY_ID,

//RECEIPT_STATUS

//FROM

//

//(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

(qvd);

NOCONCATENATE

TAB2:

LOAD CASH_RECEIPT_ID,

MAX(CASH_RECEIPT_HISTORY_ID) AS CASH_RECEIPT_HISTORY_ID_MAX

FROM

(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-

[#2]

TAB1:

LOAD CASH_RECEIPT_HISTORY_ID,

CASH_RECEIPT_ID,

RECEIPT_STATUS

FROM

(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;

[jonathandienst]

Is this what you need?

TAB1:

LOAD CASH_RECEIPT_HISTORY_ID,

CASH_RECEIPT_ID,

RECEIPT_STATUS

FROM (qvd);

LEFT JOIN(TAB1)

LOAD CASH_RECEIPT_ID,

MAX(CASH_RECEIPT_HISTORY_ID) AS CASH_RECEIPT_HISTORY_ID_MAX

FROM (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;