topic Re: maximum value require in QlikView

maximum value require

yadav_anil782 — Fri, 29 May 2015 05:58:31 GMT

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-

Re: maximum value require

Fri, 29 May 2015 06:44:21 GMT

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;

Re: maximum value require

jonathandienst — Fri, 29 May 2015 06:57:47 GMT

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;