Multi Row Formula Issue

[akhandsingh305]

Hi All, 

I have got one issue with my report where we have to identify two matching conditions and mark a result if they match.

So here we have 3 columns, ID, Cost and Cost EUR. So if the ID's match and the sum of their respective Cost makes 0 then we want 'Remove' as in Result column, else 'Remain'.

Any help would be highly appreciated.

Thanks 

[PanPP · Accepted solution]

Hi @akhandsingh305,

1) The first tool would be a Summarize tool, grouping by the ID column and sum of the cost column.

2) Use a join tool to merge back on ID (Join by specific fields)

3) Use a formula tool with the conditional logic: If [Sum_Cost] = 0 then "Remove" else "Remain" endif

Hope this helps, if it does please like this post and if it helps resolve your problem, mark it as a solution. If you have any other questions, please let us know.

[ShankerV]

Hi @akhandsingh305 

One way of solving this with only multi row formula is as below:

Input:

Workflow:

Result:

Many thanks

Shanker V

[ShankerV]

Hi @akhandsingh305 

As it was not able to achieve with singe multi row formula, used 2 as per below combination to reach the output.

1st Multi row:

2nd multi row:

Many thanks

Shanker V

[ShankerV]

@akhandsingh305 

1st multi row formula:

if ([ID]!=[Row-1:ID])
then 1
elseif ([Cost]+[Row-1:Cost]=0)
then "Remove"
else "Remain"
endif

2nd multi row formula:

if [New Field]="1"
then [Row+1:New Field]
else [New Field]
endif

Many thanks

Shanker V

[binuacs · Accepted solution]

@akhandsingh305 One way of doing this with the summarise tool

[akhandsingh305]

Thank you @panpp @shankerV and @binaucs for your quick help here.