Combine lines in Table

[Pericator]

Hello,

I've got some problems with re aggregating an existing table. E.g. I got following fields in a table:

• CallID
• LinkedID
• CallDate
• Duration
• billing_duration

In use of some phone system, there can be multiple calls, different CallIDs, with the same LinkedID. Now what I want to do, is aggregate those lines into one. So I want only one line per LinkedID including the following:

• LinkedID
• min(CallID) as CallID
• min(Calldate) as CallDate
• max(Calldate) + duration as call_end
• max(Calldate) + duration - min(Calldate) as duration
• max(billing_duration) as billing_duration

I've tried with load resident and group by LinkedID but somehow it won't work.

Thanks for your help in Advance!

[Channa]

can you share sample data and expected results

[HirisH_V7]

Try
group by LinkedID,duration;

[Pericator]

As you can see in the image below, there are multiple Entries with the same call linked ID. I want to reduce this table to one line per Linked ID and join it to some other table. This table here should keep its fields. the combined values for the result line per linked ID should be like:

• call linked ID,
• min(call time) as call time,
• max(call time)+duration (sec) - call time as duration (sec),
• max(duration billing)  as duration billing

E.g. for the first Entry, Linked ID = 1533714402.6436, I want following:

• 08.08.2018 09:46:42 as call time,
• 1533714402.6436 as call linked ID,
• 11 as duration (sec),
• 8 as duration billing (sec)

[HirisH_V7]

Check out this,

Data:
LOAD CallLinkID,
Timestamp(Min(Timestamp#(CallTime,'DD.MM.YYYY hh:mm:ss')),'DD.MM.YYYY hh:mm:ss') as CallTime, 
Min(DurSec)+Min(BillDursec) as DurSec,
Max(BillDursec) as BillDursec Group by CallLinkID;
Load *
INLINE [
CallTime, CallLinkID, DurSec, BillDursec
08.08.2018 09:46:42, 1533714402.6436, 6, 5
08.08.2018 09:46:45, 1533714402.6436, 8, 8
];

[Pericator]

I don't understand why you use Load Inline, there is more data in the same format than the example i shared.

[Pericator]

At the moment i use:

left join(AllCalls)
LOAD 
		[call linked ID],
  		min(calldate) as [call time],
    	max(calldate) + [duration (sec)] - min(calldate) as [duration (sec)],
    	max([duration billing (sec)]) as [duration billing (sec)]
Resident click2dialcalls
group by [call linked ID]
;

Sadly I get Invalid Expression error and can't see why.

[HirisH_V7]

Please share some sample data in excel to use, i.e reason i have used inline for some snippet.

[HirisH_V7]

max(calldate) + [duration (sec)] - min(calldate) as [duration (sec)],
This line is causing issue here, [duration (sec)] in between calculation will not work like that, it need to be in group by or else my suggestion to get desired result for that line:

Num#(Interval(Max(Timestamp#(CallTime,'DD.MM.YYYY hh:mm:ss'))-
Min(Timestamp#(CallTime,'DD.MM.YYYY hh:mm:ss')), 'SS'))+Max(DurSec) as
DurSec

it will also give same 11 as you required, check that logic and revert please.