topic How to show last update for each record...? in QlikView

How to show last update for each record...?

Dayna — Mon, 17 Jan 2011 11:45:15 GMT

Dear all,

I hope that you can help me, I'm trying to show in a chart the last entry (i.e. last update) for each record..

i.e. ID1 has 6 updates, I want to show the last. ID2 has 1 update, show the last (and first), ID3 has 4 updates (show the last). But I need to show this for each record, not the last overall.

I hope that you can help me!!

Kind Regards,
Dayna

How to show last update for each record...?

maneshkhottcpl — Mon, 17 Jan 2011 11:49:24 GMT

Hi,

Can u send the sample application/data?

How to show last update for each record...?

Dayna — Mon, 17 Jan 2011 12:02:05 GMT

Hello Manesh,

Please find attached.

Kind Regards,
Dayna

How to show last update for each record...?

maneshkhottcpl — Mon, 17 Jan 2011 12:31:12 GMT

Hi Dayna,

Sorry, but i need more explanation to resolve ur problem.

can u explain me wit examples.

Thanks

Manesh

How to show last update for each record...?

Dayna — Mon, 17 Jan 2011 12:39:56 GMT

Hello Manesh,

To base on the excel sheet, what I would like to show is for each SUN_ServiceReqID the last update performed. So, for the entry INC000048 the last update date/time would be 18/11/2010 13:59:16. What I'm trying to show, is the last update date/time for each ServiceReqID. So then I'd have an overview:

INC000048 - 18/11/2010 13:59:16
INC000049 - 06/12/2010 11:29:11
INC000050 - 09/12/2010 09:08:38 etc etc...

Hope this clarifies it a bit more.

Kind Regards,
Dayna

How to show last update for each record...?

Mon, 17 Jan 2011 12:52:12 GMT

Hi Dyna ,

Hope this will help you ..

LOAD

Max

(Time(SUN_IH_Update)) as Time

Max

(Date(SUN_IH_Update)) as SUN_IH_Update

SUN_ServiceReqID

FROM

(

ooxml, embedded labels, table is Sheet1) group by SUN_ServiceReqID

;

left

join

LOAD

SUN_IH_AddedBy

SUN_ServiceReqID

FROM

(

ooxml, embedded labels, table is

Sheet1);

If you have only one record that will be the max record . You will get that record using the above logic .If you want to dsiplay it as a single field just concatenate the both.

Regards,

Chakravarthy.

How to show last update for each record...?

Dayna — Mon, 17 Jan 2011 14:06:15 GMT

Hello Chakravarthy,

That looks like it's done the job! Many thanks!

Kind Regards,
Dayna

How to show last update for each record...?

Dayna — Mon, 17 Jan 2011 14:37:21 GMT

Ahh... Now that all the data is there, it doesn't seem to work anymore.. I get multiple entries for each AddedBy User... Any ideas? You're help is most appreciated!

Kind Regards,
Dayna

How to show last update for each record...?

maneshkhottcpl — Mon, 17 Jan 2011 14:58:19 GMT

Hi,

Dayna, please use the code which is send by me in application. It will work 100%.

How to show last update for each record...?

maneshkhottcpl — Mon, 17 Jan 2011 15:00:20 GMT

Dayna,

Please refer the application attached.

How to show last update for each record...?

Tue, 18 Jan 2011 02:01:51 GMT

Hi Dyna ,

Sorry , I have not noticed the user group...i.e Request Id is occupied by more than one user ...

The following code will rectify that..

LOAD

Max

(Time(SUN_IH_Update)) as Time

Max

(Date(SUN_IH_Update)) as SUN_IH_Update

SUN_ServiceReqID

SUN_IH_AddedBy

from

[Sample Data.xlsx]

(

ooxml, embedded labels, table is Sheet1) group by SUN_ServiceReqID,

SUN_IH_AddedBy;

Regards,

Chakravarthy.

How to show last update for each record...?

Tue, 18 Jan 2011 05:43:17 GMT

Hope this will help..........



Table:
LOAD //SUN_IH_AddedBy,
 max(date(SUN_IH_Update,'DD-MM-YYYY HH:mm:ss')) as SUN_IH_Update,
 SUN_ServiceReqID
FROM

(ooxml, embedded labels, table is Sheet1)
group by SUN_ServiceReqID;

inner join

LOAD SUN_IH_AddedBy,
 SUN_ServiceReqID,
 date(SUN_IH_Update,'DD-MM-YYYY HH:mm:ss') as SUN_IH_Update
FROM

(ooxml, embedded labels, table is Sheet1) ;

Regards

How to show last update for each record...?

Dayna — Tue, 18 Jan 2011 10:49:14 GMT

Hello All,

I think my problem may be more complex than I first thought.

I need the last update of a record, for any incident that has the status of 'open'. (The last update of the incident, not each user...)

I also need to ignore certain operations, including 'Add Incident', 'Open Incident', 'Resolve Incident', Assign Incident', Close Incident', Assign To Me and possibly others...

I've included an updated set of data!

Hope you can help me with my problems....
Kind Regards,
Dayna

How to show last update for each record...?

Dayna — Tue, 18 Jan 2011 10:59:33 GMT

I tried to do something like this in a straight table, but no success...:

firstsortedvalue(DISTINCT aggr(max({$<SUN_State = {'Open'}>} SUN_IH_Update),SUN_IH_Update,SUN_IH_AddedBy,SUN_IH_IncidentID,SUN_IH_Operation),-SUN_IH_Update)

Don't know if it's the way forward or not!

Kind Regards,
Dayna

How to show last update for each record...?

Tue, 18 Jan 2011 11:22:14 GMT

Hi Dayna,

see the attached example.

Maybe it gives an idea how you can get what you want.

Good luck!

Rainer

How to show last update for each record...?

Dayna — Tue, 18 Jan 2011 11:44:16 GMT

I think that'll do it!! Thank you very much Rainer, and thanks to everyone who helped!

Kind Regards,
Dayna