calculating time differences

[Anonymous]

I need to calculate the time differences between the dispensing of any two subsequent prescriptions.  The following table illustrates the situation:

ScriptNumber	DateTImeDispensed
161797	30/01/15 18:29
161798	30/01/15 18:45
161798	30/01/15 18:45
161798	30/01/15 18:45
161799	30/01/15 18:49
161800	31/01/15 08:18
161800	31/01/15 08:18
161801	31/01/15 08:39
165073	27/03/15 07:44
161802	31/01/15 09:14
161803	31/01/15 09:38
161804	31/01/15 09:41
161804	31/01/15 09:41
161804	31/01/15 09:41
161805	31/01/15 09:48
161806	31/01/15 09:55
161806	31/01/15 09:55
161806	31/01/15 09:55
161807	31/01/15 10:03
161808	31/01/15 10:19
161809	31/01/15 10:21
161810	31/01/15 10:22
161810	31/01/15 10:22
161811	31/01/15 10:28
161811	31/01/15 10:28

The ScriptNumber is a unique identifier of a prescription.  How do i calculate the time difference between any two prescriptions?

[maxgro]

ab:

LOAD ScriptNumber,

     DateTImeDispensed

FROM

[https://community.qlik.com/thread/160619]

(html, codepage is 1252, embedded labels, table is @1);

tmp:

NoConcatenate load distinct

  ScriptNumber, DateTImeDispensed

Resident ab;

ac:

load *,

  time(timestamp#(DateTImeDispensed,'DD/MM/YYY hh:mm')- Timestamp#(Peek(DateTImeDispensed),'DD/MM/YYY hh:mm')) as Delta

Resident tmp

order by ScriptNumber;

DROP Table tmp, ab;

[Anonymous]

Many thanks Massimo.  I am getting the following error message:

[maxgro]

my .qvw run without error (PFA)

maybe you have to change the "resident ab" if your table has a different name

[petter]

An alternative is to keep your data as it is in your data model and then just use a Straight Table and do the calculation there like this - see also attached QVW:

[Anonymous]

Many thanks Petter for the alternative.  My results are as follows:

i get blanks in the columns.  Is it because of the datetime format?  How can this be resolved?

[Anonymous]

Many thanks, ran without error but get the following results:

What could be the problem?

[petter]

Could be - is it possible for you to upload a sample of your appllication or share a spreadsheet with a bit of the data?

[petter · Accepted solution]

Try changing the expression to:

Dispensed-Above(Dispensed)

[maxgro]

in your first post same script number has same date

in the second same script  number has different date and the datetime format changed

better if you can add an extract of your qlikview .qvw

if you want to do the diff in script, use (adapt) my solution

if you want in chart, Petter has already answered (above function), you just have to pay attention to date time format