conditional statement, using multi-row formula

Hi everyone

I have this sample data and I'm trying to write condition using multi-row.formula tool

The condition is if [Field] = 1 then put the first row in [New Field] to 0. It might be groups of 1 but I want only the first row of each group to be 0 else null().

Requirements: the data is for a time period and each customer ID can have different number of records.

so it has to be grouped by customer ID.

also if ones are end of the observation (if customer ID changes after the last '1' , highlighted row) then null() I don't need that to be 0.

I hope I'm not too confusing.

I really appreciate your help

Thanks

Afrooz

sample data.png

Preparation

Accepted answers

Jonathan-Sherman

Hi @Afrooz,

In that case I've amended the workflow slightly to give you this result:

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.

Regards,

Jonathan

20200116 - Multirow v2.yxmd

All comments

ashissanpui

@Afrooz Here are the steps

1. Use record id at source

2. sort by Customer ID(ascending), Field (ascending), date(ascending)

3. Use multi row formula and group by customer id, field, date

4. Sue the formula in MRF if [cust ID] = [row-1:Cust ID] then null() else 0 endif

5. sort record id y ascending.

hope this will resolve your issue

Ashis

afv2688

Hello @Afrooz ,

This does it:

🙂

Edit: corrected, thanks @Jonathan-Sherman

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Regards

0_values.yxmd

Jonathan-Sherman

Hi @Afrooz,

I think this depnds how your data is structured, if all records where [Field] = 1 for each [Customer ID] are grouped together then @afv2688's solution would work perfectly, however if not i'm not sure it would work if not all 1's are grouped together as in the example picture you attached where there are two groups of 1's for [Customer ID] = 1?

For example:

Customer ID	Field
1	1
1	2
1	1
1	1

This might become:

Customer ID	Field	New Field
1	1	1
1	2	0
1	1	1
1	1	0

To solve this issue you could use:

Otherwise @afv2688 would work perfectly!

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.

Regards,

Jonathan

20200116 - Multirow.yxmd

Afrooz

Hi @Jonathon-Sherman and @afv2688

thanks for your solutions.

in the data set there might be more than one group of 1s, both solutions give me the same result which are not quiet right,

There can be more than one group of 1s for one customer ID
or it might be only one row with [Field] = 1 or even none

Both solutions return 0 for every group of 1 which is correct, but my problem is that i don't want 0 if that group of 1 leads to the end of the observation, and the customer ID changes after that last group of 1s.

For example in @Jonathan-Sherman solution the only correct answer is row 7.

In customer ID 1 and 2 New Field should not be zero because all the values in [Field] = 1

I'm sorry if I sound too confusing

would be great if you could help me with.

thank you very much for your time

Regards,

Afrooz

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703