How to fill down values to the row below in the same column?

[EtamSoko]

hi Guys, 

this is my second attempt to get some help around what is the best way to fill down from a field to the row below.And other way around. I believe that multi-row formula, 'overwritng existing field' function would do but I was not able to figure out the IF statement correctly for that.

Attached my dataset.

The task is a basic data-true up where I need all lines related to source B to look like as it looks in source A. Except if some fields related to source A are blank,then copy from source B. Important that the dataset remains in the same order based on the record ID. Also, there are much more columns in my original dataset that I cannot disclose, but the logic should apply to those columns too.

Can someone take a look into it?

I would really appreciate the help.

regards,

MateK

[RolandSchubert]

Hi @EtamSoko ,

I've created a workflow using the Multi-Row Formula tool, hopefully it is helpful.

Best,

Roland

[EtamSoko]

hi Roland,

thanks for your quick response.

However, it still does not function well, maybe I was not explaining the needs, apologies.

So basically what I would like to achieve is to copy down from all rows from source line A to B unless A is blank. If you take a look at record ID number 3, in the row for source B in serial column I would like to see 079758463, in record ID number 4, 069447038, etc. basically making a copy from all rows from source A to B.

The part for IF Null, that works perfectly.thanks!

would you be able to help me still?

thanks a lot in advance.

MateK

[RolandSchubert · Accepted solution]

I've modified the formula, but - if you say, always copy A to B, the value for RecordId 3 would be 79758463 (as this is the value for source = A). I changed the formula according to that "set A = B" requirement, but it can be reversed easily. Let me know if it works as expected. 

[EtamSoko]

Works perfectly. Much appreciated Roland!

have a nice day,

MateK

[CharlieS]

Hey @EtamSoko  @RolandSchubert 

While y'all work out the exact Multi-Row Formula, I've prepared the macros to apply this logic to as many fields and you'd like. This means you don't have to keep copying Multi-Row Formula tools and can set a list of fields you'd like this logic to be applied to. In my attached example, I use a select tool, but you can create systematic rules to define what fields should be corrected if you'd like.

I started by placing the Multi-Row Formula in a batch macro so the field name could be updated in both the tool configuration and tool expression. Then I wrapped this batch macro in an iterative macro that will loop through the list of fields you'd like this logic applied to.

Check out the attached workflow to see how this works. Once y'all finalize the Multi-Row Formula expression, update 20200227-MultiRowBatch.yxmc with the version that updates the [Serial] field and everything else should work. 

EDIT: I updated the batch macro to include @RolandSchubert's expression.

[EtamSoko]

@CharlieS

Really appreciate your suggestion, I would love to see it in my workflow.

However , I got 2 things to ask before I use it - since I have never dealt with batch macro's in my worflow.

1. As I needed to modify Roland's expression, is there any chance that I can update the batch macro as well?

2. If all will be correct, should I just copy-paste your solution into my wokflow just like anything else and select the fields I need to run the formula for?

Sorry for the elementary questions, but I am new to this topic.

Thanks a lot!

Matek

[CharlieS]

---

@EtamSoko wrote:

1. As I needed to modify Roland's expression, is there any chance that I can update the batch macro as well?

---

Of course. Inside the batch macro, there's a Multi-Row Formula tool that you can modify as you see fit. Be sure to design the expression for the [Serial] field, because that's what the macro is looking for to update. 

---

@EtamSoko wrote:

2. If all will be correct, should I just copy-paste your solution into my wokflow just like anything else and select the fields I need to run the formula for?

---

Once you have the batch macro expression as you want, the only thing you need to modify is selecting which field you want to apply it to; this is control by the tools in the tool container. If you have more than 1,000 fields you need to apply the Multi-Row Formula to, there's another change to make in the Iterative macro settings, but that's everything.

[EtamSoko]

@CharlieS 

Thanks very much. 

This is an unknown area for me but as you explained , it does not seem complicated 🙂

Appreciate your help.

All the best,

MateK