topic Re: sum distinct values with a script in load editor in App Development

sum distinct values with a script in load editor

MarZu — Thu, 13 Jul 2023 06:10:52 GMT

Hello, I want to sum the entries of the field "value" only for distinct values of another one "Sequence Id".

See below a pseudo example: the "calculate" table shall reuse previously loaded fields from the table "tag".

It sounds easy in visualisation but I have to do it in the load editor and apparently the same syntax is not available (aggr?). Please note that the it is not a matter to delete the rows which appears double.

Anyone has an Idea? How would be instead with an if ...then structure?

Thanks!!!

[calculate]:
Load
Sum(Aggr([Value]),distinct [Sequence Id])) as Counter
resident
tag;

Re: sum distinct values with a script in load editor

Chirantha — Thu, 13 Jul 2023 07:46:28 GMT

Please see if this helps

Load
[Sequence Id],
Sum([Value]) as Counter
Resident
tag
Group By
[Sequence Id];

Re: sum distinct values with a script in load editor

udit_k — Thu, 13 Jul 2023 08:22:00 GMT

Load Distinct

[Sequence Id],
Sum([Value]) as Counter
Resident
tag
Group By
[Sequence Id];

Re: sum distinct values with a script in load editor

MarZu — Thu, 13 Jul 2023 09:15:10 GMT

Hello, Thanks for the answer however it does not help me, perhaps I have missed one important info:

Imagine the values can be just 0 or 1. for the same sequnce Id I can get many rows and each can have value 0 or 1. I need to have a boolean OR over those values , so that maximum 1 can appear. the sum shall be done over all the sequences therefore the maximum expected number will be the number of the sequences.

With the proposed solution above in contrast I receive different summed values for the sequences.

Can you please help to "limit" and have max. 1 for each sequence? Thanks!

Re: sum distinct values with a script in load editor

MarZu — Fri, 14 Jul 2023 07:05:19 GMT

Hi Thank you, but it seems to produces the same result as the first solution despite DISTINCT I get counted all the values for the same single sequence.

Perhaps it helps my observation: Imagine the values can be just 0 or 1. for the same sequence Id I can get many rows and each can have value 0 or 1. I need to have a boolean OR over those values , so that maximum 1 can appear. the sum shall be done over all the sequences therefore the maximum expected number will be the number of the sequences. --> is there a way to do that? Thanks

Re: sum distinct values with a script in load editor

MarZu — Tue, 18 Jul 2023 15:33:53 GMT

Indeed I found the solution by using an intermediate step and it works as I wanted and I share it here:

Temp:

Load Distinct

[Sequence Id],

'dummy' as Dummy,

Max([Value]) as MaxCounter
Resident
tag
Group By
[Sequence Id];

Load Distinct

[Sequence Id],

Dummy,
Sum([MaxCounter]) as Counter
Resident
Temp
Group By
[Dummy];