dtの("TimeSpan")平均値を計算して、他のdtに出力したい

[gorby]

こんばんは
UiPath Studio 2024.10.5Community editionのユーザです。
下記のやりたいことのサンプルコード作成をお願いします。

1.やりたいこと

(1)下記dt_3の(“Status”)="〇"の行を(“Date”)でGroupByして、(“TimeSpan”)平均値を
dt_4の同じ年月日の行の(“Status"〇"AVG TimeSpan”)に出力。但し、(“Status”)="〇"の行が１つもない年月日の行には、(“TimeSpan”)平均値の代わりにTimeSpan.FromSeconds(0).ToStringを出力。
dt_4にはBuild Datatableした後、年月日の自動入力をしたいです。

(2)(“TimeSpan”)平均値計算結果は小数点第一位で四捨五入してください。

dt_3
[Date,Start,End,TimeSpan,Status
2024/09/21,18:38:54,18:39:12,00:00:18,×
2024/09/21,20:01:43,20:02:33,00:00:50,×
2024/09/21,20:11:21,20:11:35,00:00:14,×
2024/09/21,20:12:22,20:13:28,00:01:06,×
2024/09/21,20:28:23,20:28:38,00:00:15,×
2024/09/21,20:29:06,20:29:21,00:00:15,×
2024/09/21,20:31:21,20:31:34,00:00:13,×
2024/09/21,20:32:08,20:32:23,00:00:15,×
2024/09/21,20:34:07,20:34:19,00:00:12,×
2024/09/21,20:34:55,20:35:11,00:00:16,×
2024/09/21,20:37:46,20:38:03,00:00:17,×
2024/09/21,21:01:25,21:01:42,00:00:17,×
2024/09/21,21:18:11,21:18:16,00:00:05,×
2024/09/21,21:20:57,21:21:12,00:00:15,×
2024/09/21,21:49:53,21:50:10,00:00:17,×
2024/09/21,22:02:19,22:02:40,00:00:21,×
2024/09/22,14:00:28,14:00:56,00:00:28,×
2024/09/22,14:17:04,14:18:49,00:01:45,〇
2024/09/25,17:13:23,17:13:28,00:00:05,×
2024/09/25,17:20:52,17:20:57,00:00:05,×
2024/09/25,17:27:52,17:28:07,00:00:15,×
2024/09/25,17:49:35,17:49:45,00:00:10,×
2024/09/25,17:52:47,17:56:04,00:03:17,×
2024/09/25,17:58:58,17:59:10,00:00:12,×
2024/09/25,18:05:12,18:05:20,00:00:08,×
2024/09/25,18:12:32,18:12:36,00:00:04,×
2024/09/25,18:14:20,18:14:27,00:00:07,×
2024/09/25,18:15:25,18:15:32,00:00:07,×
2024/09/25,19:49:53,19:49:56,00:00:03,×
2024/09/25,19:50:51,19:50:55,00:00:04,×
2024/09/26,19:10:24,19:11:14,00:00:50,〇
2024/09/26,19:36:08,19:36:39,00:00:31,〇
2024/09/26,19:38:11,19:38:45,00:00:34,〇
2024/09/26,19:39:01,19:39:29,00:00:28,×
2024/09/26,19:59:18,19:59:41,00:00:23,×
2024/09/26,20:13:47,20:14:59,00:01:12,〇
2024/09/26,20:28:38,20:31:29,00:02:51,〇
2024/09/26,20:34:16,20:35:51,00:01:35,〇
2024/09/26,21:42:26,21:44:14,00:01:48,〇
2024/09/26,21:46:09,21:47:52,00:01:43,〇
2024/09/26,23:13:12,23:13:54,00:00:42,〇
2024/09/26,23:59:49,23:59:54,00:00:05,×
]

dt_4

[Date,"Status""〇""AVG TimeSpan"
2024/09/21,00:00:00
2024/09/22,00:01:45
2024/09/25,00:00:00
2024/09/26,00:01:18
]

[Anil_G (Anil Gorthi)]

@gorby

First build a datatable with teo columns and both of string type and it is dt4

Dt4 = Dt3.AsEnumerable.GroupBy(function(x) x(0).ToString).Select(function(x) dt4.LoadDataRow({x.Key,If(x.Where(function(y) y(4).ToString.Equals("0")).Count>0, Timespan.FromSeconds(x.Where(function(y) y(4).ToString.Equals("0")).Average(function(y) Timespan.Parse(y(3).TOstring).ToTalSeconds)),"00:00:00")},False)).CopyToDataTable

Cheers

[gorby]

@Anil_G

with teo columns

Is teo Typo of two ?

[Anil_G (Anil Gorthi)]

@gorby

yes thats a typo for two

cheers

[gorby]

Gi Anil_G
Thank you for your advice!
However, it seems you did not consider “round up to the nearest tenth” I expected…
I am wondering where I put “Math.round” to your suggested syntax.
How do you configure “round up to the nearest tenth” to the calculated average value?

[Anil_G (Anil Gorthi)]

@gorby

Please use math.Round and it can be done

cheers

[gorby]

Hi Anil_G

Does the next syntax make sense?

Math.round(Average(Function(y) Timespan.Parse(y(3).ToString).ToTalSeconds),0,MidpointRounding.AwayFromZero)

[Anil_G (Anil Gorthi)]

@gorby

should be like this

Timespan.FromSeconds(Math.Round(x.Where(function(y) y(4).ToString.Equals("0")).Average(function(y) Timespan.Parse(y(3).TOstring).ToTalSeconds))),0,MidpointRounding.AwayFromZero)

cheers

[gorby]

Hi Anil_G

Your suggested syntax raised three compile errors.
Pls help me.

dt4 is dt_Datatable4_work1
dt3 is dt_Datatable3

dt_Datatable4_work1=dt_Datatable3.AsEnumerable.GroupBy(function(x) x(0).ToString).Select(function(x) dt_Datatable4_work1.LoadDataRow({x.Key,If(x.Where(function(y) y(4).ToString.Equals("〇")).Count>0, Timespan.FromSeconds(Math.Round(x.Where(function(y) y(4).ToString.Equals("〇")).Average(function(y) Timespan.Parse(y(3).ToString).ToTalSeconds))),0,MidpointRounding.AwayFromZero),"00:00:00")},False)).CopyToDataTable

[gorby]

Hi Anil_G

I achieved my goal without using complicated long syntax but by using concatenated ForEachRow activity inside ForEach activity.