topic Re: calculate a datetime difference in QlikView

calculate a datetime difference

beat_roos — Wed, 11 Jul 2012 10:01:39 GMT

I do have the following Table:

DateTime	Field	oldValue	newValue
01.07.2012 - 08.04	created
01.07.2012 - 08.15	status	open	inProgress
02.07.2012 - 10.15	Owner	User1	User2
03.07.2012 - 14.50	status	inProgress	closed

now I need to calculate the total time needed for the Document. I need no know the difference from the create till the status = closed. There is only one DateTime in that history table and also other status which I do not need to calculate. Just the difference from beginning to the end.

How can I solve that? Can anybody help me to solve that?

Thanks a lot!

Re: calculate a datetime difference

swuehl — Wed, 11 Jul 2012 10:16:36 GMT

Maybe something like this?

LOAD

DocumentID,

interval(only(if(Field='status' and newValue='closed',DateTime)) - only(if(Field='created', DateTime)),'hh:mm') as DateTimeDiff

resident YOURTABLE group by DocumentID;

Re: calculate a datetime difference

beat_roos — Wed, 11 Jul 2012 11:41:03 GMT

Thank you swuehl

It works not like this. I get no result on each line and it creates a new Table "Tabelname -1".

I do not understand it really...

The original Table is CaseHistory. There I do have an ID (%CaseID) to a second Table, calling Case.

I do get the History Values per Case inside that History Table.

btw I did load the Tables from a qvd File and use qualify and unqualify % to not generate syntetic keys.

Is it easier to calculate the date-diff for each step? But how can QV now the right sort order, how to tell QV which is the first entry, the second and so on?

Thanks a lot for helping me.

My code is:

LOAD
%CaseID,
interval(only(if(CaseHistory.Field='status' and CaseHistory.NewValue='closed',CaseHistory.CreatedDate))
- only(if(CaseHistory.Field='created', CaseHistory.CreatedDate)),'hh:mm') as DateTimeDiff
resident CaseHistory group by %CaseID

;

Re: calculate a datetime difference

swuehl — Wed, 11 Jul 2012 12:55:42 GMT

I still think the approach should work.

You need to take care that your DateTime is interpreted correctly as Timestamp, e.g. by using an appropriate standard format.

You table is called "Tabelname -1", because we haven't set a specific different name, so QV just follow the name pattern of the previous table. We can correct this by explicitely stating a table name:

SET TimestampFormat='DD.MM.YYYY - hh.mm';

QUALIFY *;

UnQualify %CaseID;

CaseHistory:

LOAD * INLINE [

%CaseID, DateTime, Field, oldValue, newValue

C0815,01.07.2012 - 08.04, created, ,

C0815,01.07.2012 - 08.15, status, open, inProgress

C0815,02.07.2012 - 10.15, Owner, User1, User2

C0815,03.07.2012 - 14.50, status, inProgress, closed

];

CaseTimeDiff:

LOAD

%CaseID,

interval(only(if(CaseHistory.Field='status' and CaseHistory.newValue='closed',CaseHistory.DateTime))

- only(if(CaseHistory.Field='created', CaseHistory.DateTime)),'hh:mm') as DateTimeDiff

resident CaseHistory group by %CaseID

;