Multirow Formula

[UmarS]

Hi All,

I am trying to solve the below use case. I need to set the target column to 0 if there exists any row with 0 for a particular Deal Name column. Otherwise set the target to the amount value. Thanks for your help.

DEAL_NAME	amount	Target
1	0	0
1	150	0
1	200	0
1	15	0
2	0	0
3	0	0
3	140	0
3	50	0
4	300	0
4	0	0
5	30	30
5	31	31

[MarqueeCrew]

@UmarS,

I'd go about this a different way.  You can take the incoming data through a SUMMARIZE tool and GroupBy Deal_Name with a MIN on amount.  Join that data back to the incoming data on Deal_Name.

Now you can create a formula with something like:

IIF([min_amount] == 0,0,[amount])

Then use a SELECT tool to remove the min_amount.

I think that it reads cleanly and is easy to maintain.

Cheers,

Mark

[DataNath]

How does this look @UmarS?

[UmarS]

Thank you all. Yes it works with formula tool. However, it would be nice to make it work Multirow tool to save extra steps with formula/summarize/join tools.

[DataNath]

@UmarS there’s no real truly dynamic/efficient way to do this without extending your multi-row formula massively in either direction and then writing a massive if statement, depending on how far the deal names can go in terms of # of rows. The solutions above are fully dynamic and still very simple - there may be other ways that I can’t think of off the top of my head, but I wouldn’t personally use a Multi-Row Formula for this problem.

[oly]

@UmarS , for multi row not too hard but would probably need to sort ascending by the deal & amount. Otherwise only alternatives would be to take MIN() for multiple possible rows from min or max, that's probably what @DataNath referred earlier. 

See my solution attached with sorting, and if you need original records calculation you can add RecordID in the beginning and sort in the end again.

SUmmarize solution is rather universal and works great too.

Enjoy both solutions now!