Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

HOW TO GET LATEST RECORD BASED ON TIMESTAMP

Hi all

I have a requirement of fetching the Customer_ID with the latest time stamp, what ever being the ITEM he had purchased.

Header 1Header 2Header 3
IDITEMTIME_STAMP
123BOOKS8/18/2015 13:11
123PEN8/18/2015 13:58
123PENCIL8/19/2015 15:55
999PEN7/17/2013 16:44
777BOOKS7/27/2015 19:25
777PENCIL7/29/2015 12:01
777BOOKS7/29/2015 12:01

I HAVE TO DISPLAY THE RECORD BASED ON LATEST TIMESTAMP IN A STRAIGHT TABLE.

please can any one help...!!!

Thanks in Advance

14 Replies
MVP
MVP

Re: HOW TO GET LATEST RECORD BASED ON TIMESTAMP

Straight Table

Dimension

ID

Expression

1) Max(TIME_STAMP)

2) FirstSortedValue(ITEM,-TIME_STAMP)

Not applicable

Re: HOW TO GET LATEST RECORD BASED ON TIMESTAMP

Hi Manish Kachhia

I have already tried that,any other way please check the output as below Snip20151006_9.png

if it has same time stamp it should pick up last value and it should not be blank.

Digvijay_Singh
Honored Contributor III

Re: HOW TO GET LATEST RECORD BASED ON TIMESTAMP

MVP
MVP

Re: HOW TO GET LATEST RECORD BASED ON TIMESTAMP

if(max(TIME_STAMP)=max(TOTAL TIME_STAMP), max(TIME_STAMP))

1.png


t:

load ID, ITEM, Timestamp(Timestamp#(TIME_STAMP, 'MM/DD/YYYY hh:mm')) as TIME_STAMP

inline [

ID, ITEM, TIME_STAMP

123, BOOKS, 8/18/2015 13:11

123, PEN, 8/18/2015 13:58

123, PENCIL, 8/19/2015 15:55

999, PEN, 7/17/2013 16:44

777, BOOKS, 7/27/2015 19:25

777, PENCIL, 7/29/2015 12:01

777, BOOKS, 7/29/2015 12:01

];

Not applicable

Re: HOW TO GET LATEST RECORD BASED ON TIMESTAMP

Hi Maxgro,

I agree what you meant but how do i get other customer Id

you have shown only ID 123 how about others. please can you check once

MVP
MVP

Re: HOW TO GET LATEST RECORD BASED ON TIMESTAMP

FirstSortedValue(DISTINCT ITEM, -TIME_STAMP)

Not applicable

Re: HOW TO GET LATEST RECORD BASED ON TIMESTAMP

Thanks

MVP
MVP

Re: HOW TO GET LATEST RECORD BASED ON TIMESTAMP

if(max(TIME_STAMP)=max(TOTAL <ID> TIME_STAMP), max(TIME_STAMP))

1.png

Not applicable

Re: HOW TO GET LATEST RECORD BASED ON TIMESTAMP

Hi Manish,

Snip20151006_10.png

please can you look more in detail please, its not accepting DISTINCT and it says expression OK

Community Browser