Merging two fields into 1

[Fred]

I'm having a problem with a report I'm working on. Basicly I need to merge two columns into one.

TOTAL_ENTITLEMENT DED Merged column
Null 9,486.54 9,486.54
Null 4,699.75 4,699.75
Null 6,860.23 6,860.23
4,278.59 Null 4,278.59
Null 7,542.56 7,542.56
Null 8,182.79 8,182.79
Null 6,988.33 6,988.33
Null 5,418.67 5,418.67
Null 3,038.25 3,038.25
Null 1,610.84 1,610.84
Null 2,562.92 2,562.92
Null 12,514.81 2,514.81
Null 6,183.08 6,183.08
Null 12,120.19 2,120.19
7,567.38 Null 7,567.38
Null 9,031.24 9,031.24
Null 7,021.37 7,021.37
Null 6,760.13 6,760.13
Null 4,749.29 4,749.29
Null 9,173.00 9,173.00
Null 3,976.50 3,976.50
Null 12,542.24 2,542.24
Null 14,526.73 4,526.73
Null 4,920.69 4,920.69
Null 6,001.17 6,001.17
4,391.32 Null 4,391.32
Null 8,917.66 8,917.66 As you can see I have two different fields with the same data.
However the field called TOTAL_ENTITLEMENT was on a different line and would not trap with the field DED. I know in V7
there is an easy way to merge two fields columns into 1, but keep the data the same so basicly where the Null on the
DED field to be filled with the numbers from the TOTAL_ENTITLEMENT. I am using V6, if need be I will buy v7.

[Mike Urbonas]

I think the simplest way is to create a Calculated Field with the following very simple formula:

MAX(TOTAL_ENTITLEMENT,DED)

This will work in V6. V7's MAX function lets you compare up to four different values, though, not just two in V6.

Mike

[Fred]

Thank you Sir. smile.gif

[Grant Perkins]

Originally posted by Mike Urbonas:
I think the simplest way is to create a Calculated Field with the following very simple formula:

MAX(TOTAL_ENTITLEMENT,DED)

This will work in V6. V7's MAX function lets you compare up to four different values, though, not just two in V6.

Mike What a very interesting approach Mike! I like that.

When I have had similar situations it has usually been that one or other field would be populated so I have tended to use an IF statement. Something like;

IF([Field A] <> "blank or null", [Field A], [FIELD B])

Another way, if both possible fields are numeric but only one will have a value greater than zero (assuming fields will not be negative) is simply to add the fields together to get a final result.

At all depends ...

I like Mike's idea though. I can see interesting uses for it.

Grant

[ November 11, 2003, 01:45 PM: Message edited by: Grant Perkins ]

[Mike Urbonas]

Glad to be of help, and I will also mention that Monarch V7 enhanced the MAX function, such that up to four different fields/values can now be evaluated in one calculated field (used to be only two different values could be compared using MAX, or MIN, for that matter).

BTW, another good question came up on the forum, for which Monarch V7's new STRIP function (http://mails.datawatch.com/cgi-bin/ultimatebb.cgi?ubb=get_topic;f=1;t=000343) solved nicely (see also the new Nov. Monarch Report newsletter).

See the full list of new and improved V7 functions in the upgrade brochure: http://www.datawatch.com/pdf/products/monarch/Monarch_V7_web_brochure.pdf

[ May 19, 2006, 12:31 PM: Message edited by: Todd Niemi ]