topic Re: difference time bettween two rows in QlikView

difference time bettween two rows

neapicture — Thu, 15 Jun 2017 11:31:04 GMT

In this excel-sheet you see different informations of inbound and outbound calls (incoming time, date account) and the related action.

Now our mission is to add all inbound calls from the same account and the same action which came in within 72 hours

For example: we have to add these two calls (same account and action)

10.04.2017

13:20:11

60000242

Stammdaten

11.04.2017

10:57:25

60000242

Stammdaten

This example shows, which accounts are not allowed to added (same account, different action)

10.04.2017	13:09:49	60000240	In	Einzug
10.04.2017	13:13:23	60000240	In	Stammdaten

Does anyone knows how can I do that?

Thank y'all,

Re: difference time bettween two rows

Sergey_Shuklin — Thu, 15 Jun 2017 15:58:02 GMT

Hello, Lutz!

Use Timestamp() function and aggregation to get the hours for each key account+action:

Check - is the amount of hours. After you've got this indicator you'll be able easily separate those inbounds which are under the 72 line.

Script:

LOAD date(date) as date,starttime,acount,[In / Out],action Inline

[

date,starttime,acount,In / Out,action

42740,15:02:48,60000178,Out,Begrüßungsschreiben

42835,13:03:27,60000178,In,Umzug

42835,13:04:01,60000238,In,Einzug

42835,13:09:49,60000240,In,Einzug

42835,13:13:23,60000240,In,Stammdaten

42835,13:20:11,60000242,In,Stammdaten

42836,10:57:25,60000242,In,Stammdaten

];

Left Join(t)

LOAD

acount

,action

,(max(Timestamp(date+starttime))-min(Timestamp(date+starttime)))*24 as check

Resident t

Group by acount,action;

Re: difference time bettween two rows

lawrenceiow — Thu, 15 Jun 2017 16:47:05 GMT

Have you thought about using Peek?

This function lets you peek at the previous record as the table loads.

LOAD *, if(account = Peek('account') and action = Peek('action'), starttime - Peek('starttime'),0) as DifferenceBetweenThisRowAndPreviousRow

In other words - if the account of this row equals the account of previous row AND the action of this row equals the action of previous row then deduct the starttime of the previous row from the starttime of this row to give the difference between this row and previous row.

This expects the data in the excel-sheet to be sorted into the correct order.

Re: difference time bettween two rows

neapicture — Fri, 16 Jun 2017 11:47:36 GMT

Tx for your answer,

if for example the next call at 14.04.2017 by the same action

(42839,10:57:25,60000242,In,Stammdaten)

All check fields has the same amount of hours (93,62)

After 72 hours must start a new addition.

Pleas help

Re: difference time bettween two rows

neapicture — Mon, 19 Jun 2017 13:28:56 GMT

Tx for your answer,

if for example the next call at 14.04.2017 by the same action

(42839,10:57:25,60000242,In,Stammdaten)

All check fields has the same amount of hours (93,62)

After 72 hours must start a new addition.

Pleas help

Re: difference time bettween two rows

Sergey_Shuklin — Mon, 19 Jun 2017 17:52:48 GMT

Hello, Lutz!

Don't know if it's exactly what you need, but there is a flag field which slice periods on 72 intervals and mark them to 1,2,3 and so on.

If it's not what you want - please, contact me!

Re: difference time bettween two rows

neapicture — Tue, 20 Jun 2017 14:45:15 GMT

sorry, it's not exactly what I mean.

The flag mark them not in slice periods an 72h intervals,

Only mark the calls at the first 72 hour - please see the picture - file field new call within 72h)

A re-call after 72 h starts a new period on 72h interval

I must count the re-call whithin 72hour

Thank you for your suport

Re: difference time bettween two rows

Sergey_Shuklin — Tue, 20 Jun 2017 16:53:47 GMT

Hello, Lutz!

I don't get how you've got a 1-2-3-0-1 line for the 242 account. If you want to count recalls within the 72h intervals than the picture should be like this:

Correct me if I'm wrong.

If I've got you right - PFA.

Re: difference time bettween two rows

neapicture — Fri, 11 Aug 2017 11:46:44 GMT

Hallo Sergey,

thank you for your support, it works......

how can i filter only the action "Stammdaten" in script

Re: difference time bettween two rows

effinty2112 — Fri, 11 Aug 2017 12:54:14 GMT

Hi Lutz,

What do you mean by add the two calls? What kind of output are you looking for? You say add together calls within 72 hours of each other. If you have three calls from the same account with the same action and the calls are on Monday, Wednesday and Friday what ones do you add together?

Cheers

Andrew