maximum value require from same column

[yadav_anil782]

Dear All,

i need maximum value from ASH_RECEIPT_HISTORY_ID against CASH_RECEIPT_ID....what is need to do . plz help with sharing script for the same  coloumn. plz  share query asap it's verry urgent . i have attached excel also

AR_CASH_RECEIPT_HISTORY_ALL:

LOAD

CASH_RECEIPT_HISTORY_ID,

    CASH_RECEIPT_ID,

    RECEIPT_STATUS

FROM

(qvd);

[pokassov]

Hello!

AR_CASH_RECEIPT_HISTORY_ALL:

LOAD

    CASH_RECEIPT_ID,

     max(CASH_RECEIPT_HISTORY_ID)     as CASH_RECEIPT_HISTORY_ID,

    firstsortedvalue(RECEIPT_STATUS,-CASH_RECEIPT_HISTORY_ID)     as RECEIPT_STATUS

FROM

(qvd)

group by CASH_RECEIPT_ID;

[danieloberbilli]

just the max of the two columns?

pfa and also an example max() per ReceiptID

Data:
LOAD CASH_RECEIPT_ID, 
     CASH_RECEIPT_HISTORY_ID, 
     RECEIPT_METHOD_ID, 
     RECEIPT_NUMBER, 
     BANK_ACCOUNT_ID, 
     BANK_ACCOUNT_NAME, 
     RECEIPT_STATUS
FROM
[https://community.qlik.com/servlet/JiveServlet/download/783310-166618/excel%20data.xlsx]
(ooxml, embedded labels, table is TB09_20150527_110223);

MaxTab:
LOAD
max(CASH_RECEIPT_HISTORY_ID) as MAX_CASH_RECEIPT_HISTORY_ID,
max(CASH_RECEIPT_ID) as MAX_CASH_RECEIPT_ID
Resident Data;


MaxTab_perRecID:
LOAD
CASH_RECEIPT_ID,
max(CASH_RECEIPT_HISTORY_ID) as MAX_PER_RECID_CASH_RECEIPT_HISTORY_ID
Resident Data Group By CASH_RECEIPT_ID;

[yadav_anil782]

Thanks Сергей Покасов , now its working

[timanshu]

Kindly mark reply as answer to help others find correct answer.

[yadav_anil782]

Hi,

this script wroking only for data wich has max and min date in CASH_RECEIPT_HISTORY_ID column , but when CASH_RECEIPT_HISTORY_ID column has single value then its not picking those value. plz revert.  i need those records only from CASH_RECEIPT_HISTORY_ID which has single value against CASH_RECEIPT_ID

[yadav_anil782]

Hi,

AR_CASH_RECEIPT_HISTORY_ALL:

LOAD

    CASH_RECEIPT_ID,

     max(CASH_RECEIPT_HISTORY_ID)     as CASH_RECEIPT_HISTORY_ID,

    firstsortedvalue(RECEIPT_STATUS,-CASH_RECEIPT_HISTORY_ID)     as RECEIPT_STATUS

FROM

(qvd)

group by CASH_RECEIPT_ID;

this script wroking only for data wich has max and min date in CASH_RECEIPT_HISTORY_ID column , but when CASH_RECEIPT_HISTORY_ID column has single value then its not picking those value. plz revert.  i need those records only from CASH_RECEIPT_HISTORY_ID which has single value against CASH_RECEIPT_ID

[yadav_anil782]

dear Daniel Oberbillig,

i tryed your suggested query but its not working , plz share any other way to get correct data

[sasiparupudi1]

Please try the following script. Hope it meets your requirement.

[Data]:

LOAD CASH_RECEIPT_ID,

     CASH_RECEIPT_HISTORY_ID,

     RECEIPT_METHOD_ID,

     RECEIPT_NUMBER,

     BANK_ACCOUNT_ID,

     BANK_ACCOUNT_NAME,

     RECEIPT_STATUS

FROM

C:\sasi\qv\ReceiptData.xlsx

(ooxml, embedded labels);

//store ;

MaxTab_perRecID: 

LOAD 

RECEIPT_NUMBER,

max(CASH_RECEIPT_HISTORY_ID) as MAX_PER_RECID_CASH_RECEIPT_HISTORY_ID  

Resident Data Group By RECEIPT_NUMBER; 

[Map1]:

load CASH_RECEIPT_ID,RECEIPT_METHOD_ID,

     RECEIPT_NUMBER,

     BANK_ACCOUNT_ID,

     BANK_ACCOUNT_NAME,

     RECEIPT_STATUS Resident Data;

left join    

LOAD

RECEIPT_NUMBER, MAX_PER_RECID_CASH_RECEIPT_HISTORY_ID as CASH_RECEIPT_HISTORY_ID Resident MaxTab_perRecID;

drop Table Data;

drop Table MaxTab_perRecID;