Getting Data From Adjacent Cell

[ruby]

http://i33.tinypic.com/nl4ksj.png

Picture isn't showing up. But the top is what it is currently doing and the bottom in red is what it needs to do. Mainly it's field 5 that I am concerned with.

Field 5 is a calculated field and I'm using the formula:

Right(Field1, 4) + Right(Field4, 3)

So it takes the right most 4 digits of field 1 and combines it with the 3 digits in field 4. If a field is null in field 1 it needs to get the data above it. Does that make sense? How can you do that in Monarch?

[Grant Perkins]

Hi Ruby and welcome to the forum.

Within a single model calculated fields have to work with data from the same record - i.e. across for the row.

Assuming that your example is from the Table extracted, if your Field 1 is from an APPEND template of some sort (not the DETAIL template) then you can likely make the field fill in the table based on the value of the previous record. That should make the calculation work for you.

If the field is from the detail record that will not work. (This seems unlikely but I am including it just in case).

In that situation the most common approach would be to extract what you can and export it to a new file. Then use that file with another model and do as suggested above in order to fill the field.


Here is the relevant section of the Help file that explains the purpose of the "Copy from previous record" option.

======================================

Empty Cells options (non-calculated fields only)



Many reports are designed so that duplicate field values do not repeat down the page. When Monarch extracts the data from the report and builds a database table, it does not automatically duplicate these values, leaving empty cells throughout the field.

Leave blank (non-calculated fields only) - Select this option if you prefer to leave the empty cells blank.
Copy value from previous record (non-calculated fields only) - Select this option to copy the appropriate values into the empty cells.
======================================

If that does not lead you to a solution let us know and we can seek an alternative approach.

HTH.


Grant

[OllyInMunich]

Hello Grant,

I suspect that if the field is blank in some rows it is because it's been trapped as Detail instead of as Append.

Hello Ruby,

Please could you post a sample of the report between [ CODE] and [ /CODE] tags? The problem might be trivial if we can get the templates right, otherwise we might need a second model to make the data behave...

Best wishes,

Olly

[ruby]

That was so simple!!! Brilliant! Thanks so much for your help. Monarch seems so robust, but a little weird in figuring how it works. I'm so used to excel.

[Nick Osdale-Popa]

That was so simple!!! Brilliant! Thanks so much for your help. Monarch seems so robust, but a little weird in figuring how it works. I'm so used to excel.

The best way to think of the data when you are in Monarch is to view it as a database table, since that's exactly what it is. The rows are independent of one another, unlike Excel where you can make a row affect another.

[Grant Perkins]

The best way to think of the data when you are in Monarch is to view it as a database table, since that's exactly what it is. The rows are independent of one another, unlike Excel where you can make a row affect another.

What Nick said.

Monarch and Excel together can make a very powerful solution to most data working challenges when you a familiar with which strengths each bring to the game.

You may find the observations at www.excelwithmonarch.com (http://www.excelwithmonarch.com) help to provide a productive perspective - and more.


Grant