Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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);
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;
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;
Thanks Сергей Покасов , now its working
Kindly mark reply as answer to help others find correct answer.
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
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
dear Daniel Oberbillig,
i tryed your suggested query but its not working , plz share any other way to get correct data
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;