Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

yadav_anil782
Contributor II

maximum value require from same column

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

8 Replies
pokassov
Valued Contributor

Re: maximum value require from same column

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
Valued Contributor II

Re: maximum value require from same column

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
Contributor II

Re: maximum value require from same column

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

timanshu
Contributor III

Re: maximum value require from same column

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

yadav_anil782
Contributor II

Re: maximum value require from same column

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
Contributor II

Re: maximum value require from same column

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
Contributor II

Re: maximum value require from same column

dear Daniel Oberbillig,

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

sasiparupudi1
Honored Contributor III

Re: maximum value require from same column

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

ReceiptGroupBy.png

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