10 Replies Latest reply: Jan 7, 2017 9:02 AM by Evgeny Stuchalkin

Calculated field, based on relative search in data table

Hello! I have a table Calls like this:

Call Date DD MM YYYYAbonent
01.01.151
02.01.151
03.01.152
04.01.151

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.

• Re: Calculated field, based on relative search in data table

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

• Re: Calculated field, based on relative search in data table

Script

• Re: Calculated field, based on relative search in data table

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?

• Re: Calculated field, based on relative search in data table
Call Date DD MM YYYYAbonentDays from previous callDays before next call
01.01.15anull()1
02.01.15a12
03.01.15bnull()2
04.01.15a2null()
05.01.15b2null()
• Re: Calculated field, based on relative search in data table

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:

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:

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;

• Re: Calculated field, based on relative search in data table

Brilliant!

• Re: Calculated field, based on relative search in data table

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 YYYYAbonentDays from previous callDays before next calltf
01.01.15anull()11
02.01.15a121
03.01.15a110
04.01.15a211
05.01.15a1null()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:

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:

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;

• Re: Calculated field, based on relative search in data table

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

• Re: Calculated field, based on relative search in data table

Yes, hundreds of 0's is possible

• Re: Calculated field, based on relative search in data table

Hi Friend

Try this

Table_Tmp1:

[

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:

DateCall,

Abonent

Resident Table_Tmp1

Order By Abonent,DateCall ;

Drop Table Table_Tmp1;

UNQUALIFY*;

NoConcatenate

Table_Tmp3:

DateCall,

Abonent,

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

Resident Table_Tmp2;

Drop Table Table_Tmp2;

UNQUALIFY*;

NoConcatenate

Table_Tmp4:

DateCall,

Abonent,

PreviosCall

Resident Table_Tmp3

Order By Abonent,DateCall Desc ;

Drop Table Table_Tmp3;

UNQUALIFY*;

NoConcatenate

Table_Tmp5:

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;