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
Solved! Go to Solution.
@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
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
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
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,
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
Hi @Afrooz,
I've started from scratch and gone a slightly different way which i believe solves what you're trying to achieve? I've also taken into account where there may be more than 2 "groups" of 1s, i'm only assigning the value of 0 to the first record in the group of the last group where the group doesn't lead to the end of the observation (i've added Customer ID 5 to illustrate my point for this case) if that makes sense?
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
@Jonathan-Sherman
Thank you very much for the effort you put into this. I'm nearly there just need to get 0 in New Field in similar cases to row 21, in your example.
I you could tell me which part of to change i should be able to do it.
Thank you so so much
Afrooz
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
Thank you very much @Jonathan-Sherman
I got the answer and it works perfect.
One last thing, could you please explain a little what's happening in each step. i just want to make sure I have a good understanding of it.
Regards'
Afrooz