12 Replies Latest reply: May 15, 2014 9:58 AM by Bruce _ RSS


    JCC _

      Is there a way to change the character length limit for the calculated field expression window? I am using version 4.05....yes i know, get a newer version however the execs won't spring for it.    :mad:  Thanks for the assistance.

          Grant Perkins

          I think your best bet would be to ensure you have optimally small field names, etc. and consider if you can split the expression into multiple parts using 2 or more calculated fields.


          That assumes I have understood the the problem and speculated correctly about what might be feasible for your requirements!






            JCC _

            This is the current expression and I need to add another if statement, however there are no more character positions left. There is an 1000 character limit according to the help file.


            IF(rpt_id="GNV","GAINESVILLE",IF(rpt_id="AUG","AUGUSTA",IF(rpt_id="COL","COLUMBIA",IF(rpt_id="CHR","CHARLOTTE",IF(rpt_id="CH2","CHARLOTTE #2",IF(rpt_id="FTS","FORT SMITH",IF(rpt_id="FTW","FORT WALTON",IF(rpt_id="ALB","ALBANY",IF(rpt_id="APN","APPLETON",IF(rpt_id="JCK","JACKSONVILLE",IF(rpt_id="GN1","GR. NEBRASKA",IF(rpt_id="GN2","GR. NEB2",IF(rpt_id="GN3","GR. NEB3",IF(rpt_id="SAV","SAVANNAH",IF(rpt_id="LR2","LITTLE ROCK 2",IF(rpt_id="LRK","LITTLE ROCK",IF(rpt_id="MTG","MONTGOMERY",IF(rpt_id="NO2","NEW ORL2",IF(rpt_id="NOR","NEW ORLEANS",IF(rpt_id="OMA","OMAHA",IF(rpt_id="SPG","SPRINGFIELD",IF(rpt_id="TAL","TALLAHASSEE",IF(rpt_id="GRN","GRAND RAPIDS",IF(rpt_id="KZN","KALAMAZOO",IF(rpt_id="LAN","LANSING",IF(rpt_id="MON","MONROE",IF(rpt_id="PRL","PEARL",IF(rpt_id="SHN","SHREVEPORT",IF(rpt_id="SPA","RIDGEWAY",IF(rpt_id="TPK","TOPEKA",IF(rpt_id="OKC","OKLAHOMA CITY",IF(rpt_id="CGA","COLUMBUS",IF(rpt_id="AB2","ALBANY2",IF(rpt_id="CLE","CLEVELAND",IF(rpt_id="SV2","SAVANNAH2","")))))))))))))))))))))))))))))))))))

              Grant Perkins

              OK, ignoring the benefits you and your management could obtain from an upgrade ...


              I think you are also very close to, perhaps on,  the limit for nested IF statements that exists in V4 (if I remember correctly).


              The only obvious thing I can suggest is to shorten the rpt_id field name to something like "RID", or less, which should gain another 90 or so characters.


              After that you need to consider whether you can abbreviate the city names ... but I think the nesting limit will then be the constraining factor. (I don't have a running V4 system or the documentation available at the moment to confirm this constraint - sorry.)


              From V6 onwards you would have been able to create an internal lookup table held in the model - or an external lookup table if you had the Pro version, though for your purposes here the internal table would seem to be the ideal answer.


              Are you sure there is no way you people can be persuaded to benefit from an upgrade?


              The only other thing I can think of is to look at creating 2 calculated fields, one using half the IF formula else leave the field blank and the other the remaining part or the formula (or more fields if it really gets to be a big table.


              Only one of the fields would be populated so if you use a third calculated field to concatenate the results from the first 2 you should get the list you need.


              Would that work for you?






              • EXPRESSION LENGTH
                Nick Osdale-Popa

                JCC, since your comparison is a one-to-one relationship, you can circumvent the nested IF() statements limit as such:






                IF(rpt_id="CH2","CHARLOTTE #2","")+

                IF(rpt_id="FTS","FORT SMITH","")+

                IF(rpt_id="FTW","FORT WALTON","")+




                IF(rpt_id="GN1","GR. NEBRASKA","")+

                IF(rpt_id="GN2","GR. NEB2","")+

                IF(rpt_id="GN3","GR. NEB3","")+


                IF(rpt_id="LR2","LITTLE ROCK 2","")+

                IF(rpt_id="LRK","LITTLE ROCK","")+


                IF(rpt_id="NO2","NEW ORL2","")+

                IF(rpt_id="NOR","NEW ORLEANS","")+




                IF(rpt_id="GRN","GRAND RAPIDS","")+








                IF(rpt_id="OKC","OKLAHOMA CITY","")+




                IF(rpt_id="SV2","SAVANNAH2","")[/font][/quote]Just append new rep_id's as needed.  You should also be able to put the formula in just like above (with the line breaks) to make it easier to read/edit.


                Hope this helps!!

                • EXPRESSION LENGTH
                  JCC _

                  If I did this check properly your formula still contains over a 1,000 characters which happens to be my delimma. The calculated field expression can only contain 1,000 characters or less, from my understanding. Thanks for your input. I will find a way somehow to do this.

                  • EXPRESSION LENGTH
                    Data Kruncher



                    Grant touched on the this before, but if you replace "rpt_id" (6 characters) in Nick's sample with "rd" (or any unique 2 characters), you'll go from 1,123 characters to a svelt 983 characters, which should let you barely squeak by. Just don't add any more regions!    



                    • EXPRESSION LENGTH
                      Grant Perkins

                      Ah, nice one Nick. I seem to recall you have pointed this out before and it is a really neat way to create a complex IF requirement anyway. I can't think why I have managed not to remember it. Maybe it will stick this time!    [img]smile.gif[/img]  




                      The 'separate calculated fields and then concatenate' approach may be the most flexible answer for you.


                      If you create 2 calculated 'work' fields and, perhaps using Nick's formula split into 2 sections for future flexibility, add half of it to the formula in 'Workfield1' and the other half in 'Workfield2'.


                      Each resulting field will be populated or blank. You should never get a situation where both fields are character populated.


                      Create a third calculated field with the formula




                      Hide 'Workfield1' and 'Workfield2'


                      And that should be the job done. (Unless I have forgotten some particular limitation in V4 - but I can't think of anything that would relate to this.)






                      • EXPRESSION LENGTH
                        Nick Osdale-Popa

                        Ah, I forgot the character limit of V4 itself, so yes, using my formula to get around the nested IF() statment limitation, but also use the multiple fields as Guru Grant has suggested.


                        To note: I've recreated this in V7.02 and V8.01:

                        V7.02 has a limitation of about 3,973 characters, but the model failed to reopen after saving it.


                        V8.02 has a limitation of 4,096 characters, which as I recall is documented someplace. It had no problem reading in the xmod file after saving.

                        • EXPRESSION LENGTH
                          Grant Perkins

                          The other aspect that I think this combined solution deals with is the maximum of 36 pairs of parentheses in an expression. If my count was correct JCC you have already reached 35!


                          Using mutliple fields circumvents that problem and will be fine so long as the scale of the model is not so great that the trade-off of fields for expression definition does not cause problems with the overall maximum number of 'things' that can be managed within the workspace.


                          However, since this maximum is really quite large, the report and model would have to be  extensive and complex anyway for it to become a significant problem. In which case it could well prove beneficial to review the approach and split it up a bit for ease of processing and maintenance.


                          HTH. Do let us know when you manage to get the problem sorted JCC.


                          Apologies to all at Datawatch if this provides another excuse for a customer not to take advantage of an upgrade.   

                          • EXPRESSION LENGTH
                            JCC _

                            OK, I know this was an old post but I am back to my delimma and I am havng to add a 20 or so new markets.   :mad:   So my ? is, Nick I did not quite understand the line break thing as I copied your formula directly into a new c.f. and it did not look like I guess it should have, like yours easy to read. Second, will I just be able to add on with no character limitations or will I still have a 1k limit? Grant, I am not sure exactly what you mean or how to do that, with your idea, and is it based off of Nick's formula? Fyi, I did replace "rpt_id" with just "rd" and that helped a lot. Thanks again for your support.

                            • EXPRESSION LENGTH
                              Grant Perkins

                              Hi JCC,


                              Re the Line Breaks - I think Nick will confirm this, if you paste a copied formula it will automatically remove 'spaces' and formatting to compress the formula. However all you need to do to make a long formaula more readable is anter a few returns where you think they are appropriate to get a more readable appearance. It makes no difference to the formula or its working - just easier to read.


                              For the multiple field idea - it's just cutting up the problem into manageable bites.


                              So if you get a list of markets - lets say you have 70 to deal with - create one calculated field that translates the first xx of them (say the first 35 codes) and leaves the field BLANK if it finds no match. Create a second calculated field for the next 35 codes and again leave the result BLANK if there is no match.


                              Now you want to combine the results of both separate fields into a single field, so simply concatenate the two calculated fields, only on of which will have a value in it. Create a new calculated field combining the previous 2 is the one you want - Field1 + Field2.


                              You can 'hide' the other two so they are not a visible part of the table.


                              Now is you find that the previous fields are at the capacity of the formula/character availability but you need to add even more markets - no problem. Cretae another calcualted (and 'to be hidden' field) to translate the new codes and then modify the concatenation field to combine al three hidden fields. And so on as things expand.


                              A could think of other uses for the hidden calculated fields. For example if the codes are grouped together for other purposes - say reporting by manager responsible - you could organise your calcualted field groups in such a way they would make good simple filters as well. If hidden field 1 is not blank then report is to  manager A, if field 2 is not blank report it to manager B and so on.


                              Teh only thing to watch out for on concatenation is the possibility you may get extra blanks in a field which might be undesirable for fthe format of the display or for some other later calculated field proceses using these hidden fields. Fortunately Monarch provides the TRIM functions (TRIM, RTRIM and LTRIM and also INTRIM added at  Version 8)  to help remove any difficult to spot blanks which may cause problems.


                              I hope this helps. It is an easy concept to implement but perhaps not so easy put into words. I had to think about it myself after reading my previous description! This one makes perfect sense to me at the moment - whether it will later is another matter ...       




                              • EXPRESSION LENGTH
                                Bruce _



                                This would be a great case to get the money for the upgrade to version 8. You would find that this problem would be easy to manage with a lookup (internal or external) to translate these cities.


                                However if your boss won't spring for the upgrade, you're stuck doing it the hard way.