Hi Olly and Joey, thank you both for your suggestions.
The CTOD("") works with the if statement although it produces an error when you attempt to test (which doesn't bother me).
I'd love to do away with as many nested if statements as possible so I am intrigued by the idea of a lookup table...can you either of you gentlemen tell me more about how lookup tables work and are setup in Monarch (I'm using 10.0 pro). If necessary I'll hit the help file, I'm just asking in case there's a better resource.
I love this product and the members of this forum are awesome!
Replacing nested If with lookup tables is always a good idea, but because Robert is using multiple fields for the calculation, I'm not certain that a lookup table is a feasible option in this case.
If only one of your 15 conditions can the AX value at the same time, then restructuring your expression might make it more readable and equally functional (while decidedly lengthy):
/codeAlternatively, I'd split this into two fields; one a character field to return either "" or the required date (as a string using DtoC), and the other to finally convert "" to a null, or the string date to a short Date using CtoD.
Well, if you're being so kind as to open the door, I may as well walk through!
[URL="http://********************/calcfield/comparing-monarchs-internal-and-external-lookups"]On comparing internal and external lookups[/URL], which should provide the insight that you need.
Good Afternoon Gentlemen, it's always good to hear from you two!
Grant, you are correct, I'm breaking a potential 15 value multiline into pieces then scanning it for the codes and dates I need.
Unfortunately, the end users want the 15 lines stored individually but I like the look of your code suggestion and will look to see if I can incorporate it.
Data Kruncher, I have your website as a permanent favorite and intend to include it as a reference on internal training materials (with proper citation of course)! lol The help file, this forum, and your site are the means I used to learn Monarch.
Thanks to the help you both (and Olly) provided previously, a single model replaced 2 access databases and several hours of work...We can now process a report with 2 million records in about 15 minutes (including 70 calculated fields).
I apparently have much more to learn.
Thank you all for your help!
That's music to my ears, Robert! I'm thrilled to hear that you find the site to be an invaluable part of your experience with Monarch. :cool:
The more I learn, the more I realize that there's much more to learn. It's all good fun.
You made an important point: it's not just what you can do, but also how quickly the work can be done. For me, the first big personal win was in replacing literally hours of detailed and stressful work with a few minutes of Monarch work together with Excel to produce exactly the same end result.
IMO, the opportunity is in taking the time that you get back and doing even more with it.
I think Olly gives the answer to your main question here but reading the description of what you are doing I was just wonder which way you are using TextLine.
Am I right to assume that you have set up some fields for each of the CODE values - so say 15 fields - and you then wish to populate the field for "AX" with its corresponding date IF one of the lines contains the code AX at a certain position in the line? I assume implicitly that only one line will have the AX code in any individual record.
If my guess is wrong you can stop reading here!
If not you could consider the 'Search' mode of TextLine which should eliminate the need to use such a long nested IF statement.
So late's say you have a line something like
that might appear in any of the 15 or so line that may exist for the record.
If you just wanted the date ....
would give you just the date (... as text. You may need to manipulate it with additional findtions to give a different format if that is what you want.)
If the uniqueness of "AX" relates to its position on a line - which I might well do in some situations - then you can apply other functions to create an Expression that specifies the positional uniqueness for the search as a substitute for the simple "AX" input.
gives just the date from the example line
I don't know that this will be of any use to you but though I would offer it as a suggestion just in case it is.