Calculated field, based on relative search in data table

[evgeniystuchalk]

Hello! I have a table Calls like this:

Call Date DD MM YYYY	Abonent
01.01.15	1
02.01.15	1
03.01.15	2
04.01.15	1

Table sorted accending by Call Date.

For each call record i want calculate two fields:

How many days passed since last call from abonent,

How many days passed before next call happens.

For example, for Call at 02.01.15 I weant to see: 1 day from last call, 2 days before next call.

How to do this? I have not Idea.

[sunny_talwar]

You want to do this in the script or front end?

[evgeniystuchalk]

Script

[sunny_talwar]

I think this is doable using Peek/Previous, but would you be able to give the exact output you are looking to see in the new column or columns?

[evgeniystuchalk]

Call Date DD MM YYYY	Abonent	Days from previous call	Days before next call
01.01.15	a	null()	1
02.01.15	a	1	2
03.01.15	b	null()	2
04.01.15	a	2	null()
05.01.15	b	2	null()

[sunny_talwar · Accepted solution]

This:

Table:

LOAD * INLINE [

    Call Date DD MM YYYY, Abonent

    01.01.15, a

    02.01.15, a

    03.01.15, b

    04.01.15, a

    05.01.15, b

];

TempTable:

LOAD *,

  If(Abonent = Previous(Abonent), RangeSum([Call Date DD MM YYYY], -Previous([Call Date DD MM YYYY]))) as [Days from previous call]

Resident Table

Order By Abonent, [Call Date DD MM YYYY] asc;

FinalTable:

LOAD *,

  If(Abonent = Previous(Abonent), RangeSum(Previous([Call Date DD MM YYYY]), -[Call Date DD MM YYYY])) as [Days before next call]

Resident TempTable

Order By Abonent, [Call Date DD MM YYYY] desc;

DROP Table TempTable, Table;

[evgeniystuchalk]

Brilliant!

[handry_orozco]

Hi Friend

Try this

Table_Tmp1:

LOAD * Inline

[

DateCall, Abonent

01/01/2015,a

02/01/2015,a

03/01/2015,b

04/01/2015,a

05/01/2015,b

];

UNQUALIFY*;

NoConcatenate

Table_Tmp2:

  LOAD

  DateCall,

  Abonent

  Resident Table_Tmp1

  Order By Abonent,DateCall ;

  Drop Table Table_Tmp1;

UNQUALIFY*;

NoConcatenate

Table_Tmp3:

  LOAD

  DateCall,

  Abonent,

  If(Peek(Abonent) =Abonent, DateCall - Peek(DateCall), Null()  ) AS PreviosCall

  Resident Table_Tmp2;

  Drop Table Table_Tmp2;

UNQUALIFY*;

NoConcatenate

Table_Tmp4:

  LOAD

  DateCall,

  Abonent,

  PreviosCall

  Resident Table_Tmp3

  Order By Abonent,DateCall Desc ;

  Drop Table Table_Tmp3;

  UNQUALIFY*;

NoConcatenate

Table_Tmp5:

  LOAD

  DateCall,

  Abonent,

  PreviosCall,

  If(Peek(Abonent) =Abonent, Peek(DateCall) - DateCall, Null()  ) AS BeforeCall

  Resident Table_Tmp4

  Order By Abonent,DateCall Desc ;

  Drop Table Table_Tmp4;

[evgeniystuchalk]

One more question. I also have parameter, that points records i need to skip. It calls tf

I need calculation work like this:

Call Date DD MM YYYY	Abonent	Days from previous call	Days before next call	tf
01.01.15	a	null()	1	1
02.01.15	a	1	2	1
03.01.15	a	1	1	0
04.01.15	a	2	1	1
05.01.15	a	1	null()	1

record from 03.01.15 must be skipped in calculation of Days from previous call and Days before next call

i'm trying change code like this, but it dont work properly. What to do?

Table:

LOAD * INLINE [

    Call Date DD MM YYYY, Abonent,tf

    01.01.15, a, 1

    02.01.15, a, 1

    03.01.15, a, 0

    04.01.15, a, 1

    05.01.15, a, 1

];

TempTable:

LOAD *,

  If(Abonent = Previous(Abonent) and Previous(tf)=1, RangeSum([Call Date DD MM YYYY], -Previous([Call Date DD MM YYYY]))) as [Days from previous call]

Resident Table

Order By Abonent, [Call Date DD MM YYYY] asc;

FinalTable:

LOAD *,

  If(Abonent = Previous(Abonent) and Previous(tf)=1, RangeSum(Previous([Call Date DD MM YYYY]), -[Call Date DD MM YYYY])) as [Days before next call]

Resident TempTable

Order By Abonent, [Call Date DD MM YYYY] desc;

DROP Table TempTable, Table;

[sunny_talwar]

Is it possible to have more than 3-4 rows of 0's? Looking to understand the data so that I can work on a more generalized solution here