topic Re: maximum value require from same column in QlikView

maximum value require from same column

yadav_anil782 — Wed, 27 May 2015 06:04:54 GMT

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

Re: maximum value require from same column

pokassov — Wed, 27 May 2015 06:13:38 GMT

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;

Re: maximum value require from same column

danieloberbilli — Wed, 27 May 2015 06:14:00 GMT

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;

Re: maximum value require from same column

yadav_anil782 — Wed, 27 May 2015 06:43:09 GMT

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

Re: maximum value require from same column

timanshu — Wed, 27 May 2015 06:48:43 GMT

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

Re: maximum value require from same column

yadav_anil782 — Wed, 27 May 2015 07:33:56 GMT

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

Re: maximum value require from same column

yadav_anil782 — Wed, 27 May 2015 07:34:33 GMT

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;

Re: maximum value require from same column

yadav_anil782 — Wed, 27 May 2015 08:56:04 GMT

dear Daniel Oberbillig,

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

Re: maximum value require from same column

sasiparupudi1 — Fri, 29 May 2015 12:00:42 GMT

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;