Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
yadav_anil782
Contributor

maximum value require

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 Replies
Not applicable

Re: maximum value require

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;

MVP
MVP

Re: maximum value require

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;

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Community Browser