Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

MalcolmCICWF
Contributor II

Keeping only the Record that has the Max Create Date

I want to left join a new table to a current table I have. My new table I am pulling from an existing Qvd, has multiple records for an account number. What is the easiest way, in my report, to only pull the record that is the max CreatedDate, for each unique account number? I need the status (3rd column) from that most recent record.

Capture.JPG.jpg

9 Replies
anbu1984
Honored Contributor III

Re: Keeping only the Record that has the Max Create Date

Load AcctNo,FirstSortedValue(Status,-Timestamp#(CreatedDate,'YYYY-MM-DD hh:mm:ss.fff')) From Qvd Group by AcctNo

MVP
MVP

Re: Keeping only the Record that has the Max Create Date

t:          // test data

load AcctNo, Timestamp#(CreatedDate,'YYYY-MM-DD hh:mm:ss.fff') as CreatedDate, Status inline [

AcctNo, CreatedDate, Status

1, 1-1-2013 22:27:48610, a

1, 1-1-2015 22:27:48611, b

2, 1-1-2012 22:27:48610, c

2, 1-1-2010 22:27:48610, d

2, 1-8-2010 22:27:48610, e

2, 1-10-2010 22:27:48611, f

];

t2:

NoConcatenate

load *

Resident t

where peek(AcctNo) <> AcctNo                // first AcctNo by CreatedDate desc

order by AcctNo, CreatedDate desc;

DROP Table t;

MalcolmCICWF
Contributor II

Re: Keeping only the Record that has the Max Create Date

I tried this, but it is saying invalid expression. Maybe I did not set it up properly, but I looks like I did. Am I missing something?


LOAD

EXTR_ACCT_ID,FirstSortedValue(EXTR_ID,-Timestamp#(EXTR_CREATEDDATE,'YYYY-MM-DD hh:mm:ss.fff')) ,

EXTR_ID as Collect_Score

FROM [..\..\..\Qvd Applications\QVD_EXTR.qvd](qvd)

Group by EXTR_ACCT_ID;

 

Re: Keeping only the Record that has the Max Create Date

Hi,

EXTR_ID is missing in the group by clause:



LOAD

EXTR_ACCT_ID,FirstSortedValue(EXTR_ID,-Timestamp#(EXTR_CREATEDDATE,'YYYY-MM-DD hh:mm:ss.fff')) ,

EXTR_ID as Collect_Score

FROM [..\..\..\Qvd Applications\QVD_EXTR.qvd](qvd)

Group by EXTR_ACCT_ID, EXTR_ID;



Hope this helps


regards


Marco

MVP
MVP

Re: Keeping only the Record that has the Max Create Date

Hi,

Try like this

Data:

LOAD

*

WHERE Flag = 1;

LOAD

*,

If(peek(AcctNo) <> AcctNo, 1, 0) AS Flag

FROM DataSource

ORDER BY AccountNumber, Date Desc;

Hope this helps you.

Regards,

Jagan.

jyothish8807
Honored Contributor II

Re: Keeping only the Record that has the Max Create Date

Hi,

Try this:

Data:

LOAD

*

WHERE Flag = 1;

LOAD

*,

If((AcctNo) <> previous(AcctNo), 1, 0) AS Flag

FROM DataSource

Order by   AccountNumber,Date Desc;

Regards

KC

Best Regards,
KC
anbu1984
Honored Contributor III

Re: Keeping only the Record that has the Max Create Date

If you are looking for only status for maximum date per Account number, then remove this line from your script

EXTR_ID as Collect_Score

MalcolmCICWF
Contributor II

Re: Keeping only the Record that has the Max Create Date

can anyone Explain how these are working exactly to pull the most recent? I am interested in understanding how it functions.

anbu1984
Honored Contributor III

Re: Keeping only the Record that has the Max Create Date

From QV help

firstsortedvalue ([ distinct ] expression [, sort-weight [, n ]])

Returns the first value of expression sorted by corresponding sort-weight when expression is iterated over a number of records as defined by a group by clause. Sort-weight should return a numeric value where the lowest value will render the corresponding value of expression to be sorted first. By preceding the sort-value expression with a minus sign, the function will return the last value instead. If more than one value of expression share the same lowest sort-order, the function will return NULL. By stating an n larger than 1, the nth value in order will be returned. If the word distinct occurs before the expression, all duplicates will be disregarded.

LOAD

EXTR_ACCT_ID,FirstSortedValue(EXTR_ID,-Timestamp#(EXTR_CREATEDDATE,'YYYY-MM-DD hh:mm:ss.fff'))

FROM [..\..\..\Qvd Applications\QVD_EXTR.qvd](qvd)

Group by EXTR_ACCT_ID;

FirstSortedValue(EXTR_ID,Timestamp#(EXTR_CREATEDDATE,'YYYY-MM-DD hh:mm:ss.fff')) -- Within each group of EXTR_ACCT_ID, EXTR_CREATEDDATE is sorted in ascending order and selects EXTR_ID for the minimum EXTR_CREATEDDATE

FirstSortedValue(EXTR_ID,-Timestamp#(EXTR_CREATEDDATE,'YYYY-MM-DD hh:mm:ss.fff')) -- If minus is used in sort expression then Within each group of EXTR_ACCT_ID, EXTR_CREATEDDATE is sorted in descending order and selects EXTR_ID for the maximum EXTR_CREATEDDATE

Community Browser