topic HOW TO GET LATEST RECORD BASED ON TIMESTAMP in QlikView

HOW TO GET LATEST RECORD BASED ON TIMESTAMP

Tue, 06 Oct 2015 18:39:42 GMT

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 1	Header 2	Header 3
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

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

please can any one help...!!!

Thanks in Advance

Re: HOW TO GET LATEST RECORD BASED ON TIMESTAMP

MK_QSL — Tue, 06 Oct 2015 18:41:09 GMT

Straight Table

Dimension

Expression

1) Max(TIME_STAMP)

2) FirstSortedValue(ITEM,-TIME_STAMP)

Re: HOW TO GET LATEST RECORD BASED ON TIMESTAMP

Tue, 06 Oct 2015 18:58:15 GMT

Hi Manish Kachhia

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

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

Re: HOW TO GET LATEST RECORD BASED ON TIMESTAMP

Digvijay_Singh — Tue, 06 Oct 2015 19:06:12 GMT

Duplicate of getting the latest record based on time stamp

Re: HOW TO GET LATEST RECORD BASED ON TIMESTAMP

maxgro — Tue, 06 Oct 2015 19:09:38 GMT

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

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

];

Re: HOW TO GET LATEST RECORD BASED ON TIMESTAMP

Tue, 06 Oct 2015 19:20:50 GMT

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

Re: HOW TO GET LATEST RECORD BASED ON TIMESTAMP

MK_QSL — Tue, 06 Oct 2015 19:24:24 GMT

FirstSortedValue(DISTINCT ITEM, -TIME_STAMP)

Re: HOW TO GET LATEST RECORD BASED ON TIMESTAMP

Tue, 06 Oct 2015 19:24:44 GMT

Thanks

Re: HOW TO GET LATEST RECORD BASED ON TIMESTAMP

maxgro — Tue, 06 Oct 2015 19:31:14 GMT

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

Re: HOW TO GET LATEST RECORD BASED ON TIMESTAMP

Tue, 06 Oct 2015 19:38:55 GMT

Hi Manish,

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

Re: HOW TO GET LATEST RECORD BASED ON TIMESTAMP

MK_QSL — Tue, 06 Oct 2015 19:57:17 GMT

ignore red lines.. It's a bug which has to be fixed by QlikTech.. You just use the expression. You should get the result.

Re: HOW TO GET LATEST RECORD BASED ON TIMESTAMP

Wed, 07 Oct 2015 04:03:49 GMT

Hi Manish,

Even though am not getting output please can you check any other alternative please

Thanks

Re: HOW TO GET LATEST RECORD BASED ON TIMESTAMP

MK_QSL — Wed, 07 Oct 2015 08:33:12 GMT

Let me know after checking the enclosed file..

Re: HOW TO GET LATEST RECORD BASED ON TIMESTAMP

Wed, 07 Oct 2015 14:21:32 GMT

Hi Manish

How do we remove null values for a expression for straight table in qlik view

Thanks

Re: HOW TO GET LATEST RECORD BASED ON TIMESTAMP

MK_QSL — Wed, 07 Oct 2015 15:27:27 GMT

May be something like below

IF(IsNull(YourExpression),0,YourExpression)

Tick Suppress Zero Values in Presentation Tab