topic Re: subtract time in same date field in QlikView

subtract time in same date field

Anonymous — Tue, 04 Mar 2014 18:46:56 GMT

hi,

i have scenario having date field and phone numbers repeating thrice/ 4 times, i want to find interval between each of them; how can i find it in same date column and show them in chart

table format:

phno time

1234567890 3/4/2013 08:00:00

1234567890 3/5/2013 09:00:00

1234567890 3/5/2013 15:00:22

Re: subtract time in same date field

Tue, 04 Mar 2014 18:50:58 GMT

can you please provide the sample expected output

Re: subtract time in same date field

Anonymous — Tue, 04 Mar 2014 19:00:16 GMT

It is something like want to show bars for 3/4 times for same ph no and showing time between them as trend

Re: subtract time in same date field

Anonymous — Tue, 04 Mar 2014 19:50:03 GMT

try this:

load the table grouped by phone number.

in your table, add this field:

if(phno <> peek (phno,-1), 0, calltime - peek(calltime, -1)) as difference

in a bar chart, use phno and calltime as dimensions and sum(difference) as expression.

Re: subtract time in same date field

maxgro — Tue, 04 Mar 2014 19:53:02 GMT

below you find the script to generate interval;

my date format is SET DateFormat='DD/MM/YYYY';

I don't understand the chart you would like to have

load

phno,

time,

if(phno<>peek(phno), null(), interval(time - peek(time), 'DD HH mm ss')) as timediff,

if(phno<>peek(phno), null(), interval#(time - peek(time))) as timediff2;

LOAD * INLINE [

phno , time

1234567890 ,3/4/2013 08:00:00

1234567890 ,3/5/2013 09:00:00

1234567890 ,3/5/2013 15:00:22

];

Re: subtract time in same date field

Tue, 04 Mar 2014 19:57:32 GMT

Hey Praveena,

Check out the attached doc and see if it helps.

Thanks