Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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-
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;
Is this what you need?
TAB1:
LOAD CASH_RECEIPT_HISTORY_ID,
CASH_RECEIPT_ID,
RECEIPT_STATUS
FROM
LEFT JOIN(TAB1)
LOAD CASH_RECEIPT_ID,
MAX(CASH_RECEIPT_HISTORY_ID) AS CASH_RECEIPT_HISTORY_ID_MAX
FROM
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;