Gareth Horton
July 12th, 2002, 01:34 PM
Although Monarch does not use all of the same functions as VB, there are many that are basically easier to use (wait until you see Monarch 6!). To produce a field with the remainder of a field from the third position on: Use the Substr(,,) function. Substr([field name],3,50). Just make sure you are working with a character field and that you indicate a large enough value in the expression to produce all of the data. Unfortunatly, this can be a problem when trying to work with memo fields. The maximum character field length is 254 characters. So, longer fields will be truncated.
Dee Moore
Datawatch Corporation
Technical Support
978.441.2200
Mark Huston wrote:
> Mark Huston wrote:
>
> I meant to post this before, but I hit "enter" and sent it before I could attach my reply.
>
> I agree that a repository would be valuable. Todd, are you offering to host a web site for the repository? Anyone else?
>
> Gareth, I disagree that editing out the proprietary data is difficult. Sure, you need to modify your reports to scramble proprietary data. Here's what I use to share reports. Open the report in Word. If the layout changes, try changing the font to Courier New or some other non-proportional font. Then use Edit > Replace.
>
> Replace 1, 2, 3, 4, and 5 with 9's, replace 6, 7, 8, and 9 with 1. Then you have all 1's, 9's, and zeroes. You an also replace any company headers, etc. by the same technique. Your model still works but the proprietary information is removed.
>
> I would, however, like to see more examples of calculated fields (hopefully simpler than these). I was struggling with a calculated field that would remove the left two characters in a variable-length string. In VBA, it would look something like =Left(MyField,Len(MyField)-2)) but I couldn't get it to work in Monarch.
>
> My models are relatively simple, and I only use the most basic calculated fields and summary functions. I, too, use Monarch as the "go-between" from reports to Access and Excel. Frankly, I find that it's quicker and more flexible to automate a few processes in Excel to get to the end result than to build a lot of functions into the Monarch summary.
>
> My $0.02,
> Mark!
> > Gareth Horton wrote:
> > > Todd Frisch wrote:
> > > > I would like to make a suggestion to enduser's of Monarch - why don't we create
> > > > a Repository for models, expressions, and examples of our efforts with solutions.
> > > >
> > > > I find Monarch to be one of the most amazing pieces of software I have ever used but it is also
> > > > one of the most difficult to learn as well.
> > > >
> > > > I hope I am not the only user who has captilized on Datawatch's wonderful
> > > > Monarch course but still finds it slow and difficult to recall infrequently used model skills
> > > > to come up with a solution.
> > > >
> > > > I feel I could significantly increase Monarch's use in my position and throught out my
> > > > organization if I could sink my teeth into some advanced examples of how to use this software.
> > > >
> > > > Additionally , if I could see more examples and they had more depth I could move
> > > > away from other pieces of software and focus more on using
> > > > Monarch more extensively. Currently I use monarch primarily as a go between to
> > > > our systems and excel or access.
> > > >
> > > > What do you think - "End User's" and "Datawatch people".
> > > >
> > > > Todd Frisch, Forecast & Sales Analyst, K'nex Industries
> > > > tfrisch@knex.com
> > >
> > > Hi Todd,
> > >
> > > I have thought about this myself before, but the main problem is
> > > the privacy of data. When we look at reports at Datawatch, we make
> > > absolutely sure that customer data is not made available in any way and
> > > often we will sign non-disclosure agreements with customers before looking
> > > at the reports.
> > >
> > > Well, what about "scrubbed" reports, you may ask. The problem here is that
> > > it is incredibly time consuming to do this properly, cutting the report properly to
> > > a sizable length so that it still makes logical sense and then painstakingly replacing
> > > every item of data on the report with bogus data that still makes sense when filtered or
> > > summarised for example.
> > >
> > > Obviously, very basic examples can be mocked up in a text editor, but usually something
> > > really illuminating goes beyond that.
> > >
> > > As regards expressions, you tend to find that many of them are very specific to a particular
> > > task and even to a particular organization, however there are exceptions, fairly generic expressions
> > > which would be useful to list. I have only come across a few myself.
> > >
> > > I will contribute a fairly strange and unique expression I once had to write to strip out double spaces
> > > from text and reduce them back down to single spaces, whilst maintaining the single spaces in the text correctly. It's fairly involved, and I don't think anyone else should be subjected to working it out again. There is doubtless a more elegant way to do it. Just replace <Your Field Here> with the field you need to format.
> > >
> > > Left((Left((ltrim(<Your Field Here>)),(Instr(" ",(ltrim(<Your Field Here>)))))+,$s:LF> If((Instr(" ",(ltrim(<Your Field Here>)))<>0),,$s:LF> (Substr((ltrim(<Your Field Here>)),Instr(" ",(ltrim(<Your Field Here>)))+2)),(ltrim(<Your Field Here>)))),(Instr(" ",(Left((ltrim(<Your Field Here>)),(Instr(" ",(ltrim(<Your Field Here>)))))+,$s:LF> If((Instr(" ",(ltrim(<Your Field Here>)))<>0),,$s:LF> (Substr((ltrim(<Your Field Here>)),Instr(" ",(ltrim(<Your Field Here>)))+2)),(ltrim(<Your Field Here>)))))))+
> > > If((Instr(" ",(Left((ltrim(<Your Field Here>)),(Instr(" ",(ltrim(<Your Field Here>)))))+,$s:LF> If((Instr(" ",(ltrim(<Your Field Here>)))<>0),,$s:LF> (Substr((ltrim(<Your Field Here>)),Instr(" ",(ltrim(<Your Field Here>)))+2)),(ltrim(<Your Field Here>)))))<>0),,$s:LF> > (Substr((Left((ltrim(<Your Field Here>)),(Instr(" ",(ltrim(<Your Field Here>)))))+,$s:LF> If((Instr(" ",(ltrim(<Your Field Here>)))<>0),,$s:LF> (Substr((ltrim(<Your Field Here>)),Instr(" ",(ltrim(<Your Field Here>)))+2)),(ltrim(<Your Field Here>)))),Instr(" ",(Left((ltrim(<Your Field Here>)),(Instr(" ",(ltrim(<Your Field Here>)))))+,$s:LF> If((Instr(" ",(ltrim(<Your Field Here>)))<>0),,$s:LF> (Substr((ltrim(<Your Field Here>)),Instr(" ",(ltrim(<Your Field Here>)))+2)),(ltrim(<Your Field Here>)))))+2)),(Left((ltrim(<Your Field Here>)),(Instr(" ",(ltrim(<Your Field Here>)))))+,$s:LF> If((Instr(" ",(ltrim(<Your Field Here>)))<>0),,$s:LF> (Substr((ltrim(<Your Field Here>)),Instr(" ",(ltrim(<Your Field Here>)))+>> > >
> > > Here's the most accurate conversion from julian to gregorian I know - just replace [Julian_Input] with your
> > > input Julian date field. This is based on the algorithm of Henry F. Fliegel and Thomas C. Van Flandern. The Julian date input must be in numeric format, with no decimal places (denoting time) and the calculated field must be a date format.
> > >
> > > Ctod(Str(Int((Int(80*(Int((Int([Julian_Input])+68569)-Int((146097*(Int((4*(Int([Julian_Input])+68569))/146097))+3)/4)-Int(1461*(Int(4000*((Int([Julian_Input])+68569)-Int((146097*(I nt((4*(Int([Julian_Input])+68569))/146097))+3)/4)+1)/1461001))/4)+31))/2447))+2-12*(Int((Int(80*(Int((Int([Julian_Input])+68569)-Int((146097*(Int((4*(Int([Julian_Input])+68569))/14 6097))+3)/4)-Int(1461*(Int(4000*((Int([Julian_Input])+68569)-Int((146097*(Int((4*(Int([Julian_Input])+68569))/146097))+3)/4)+1)/1461001))/4)+31))/2447))/11))),2,0)+"/"+Str((Int((In t([Julian_Input])+68569)-Int((146097*(Int((4*(Int([Julian_Input])+68569))/146097))+3)/4)-Int(1461*(Int(4000*((Int([Julian_Input])+68569)-Int((146097*(Int((4*(Int([Julian_Input])+68 569))/146097))+3)/4)+1)/1461001))/4)+31))-Int(2447*(Int(80*(Int((Int([Julian_Input])+68569)-Int((146097*(Int((4*(Int([Julian_Input])+68569))/146097))+3)/4)-Int(1461*(Int(4000*((Int ([Julian_Input])+68569)-Int((146097*(Int((4*(Int([Julian_Input])+68569))/146097))+3)/4)+1)/1461001))/>> > >
> > > Int((1461 * (Val(Right([MDYDate],4)) + 4800 + Int((Val(Left([MDYDate],2)) - 14) / 12))) / 4) +
> > > Int((367 * (Val(Left([MDYDate],2)) - 2 - 12 * Int((Val(Left([MDYDate],2)) - 14) / 12))) / 12) -
> > > Int((3 * (Int(Val(Right([MDYDate],4)) + 4900 + Int((Val(Left([MDYDate],2)) - 14) / 12)) / 100)) / 4) + Val(Substr([MDYDate],4,2)) - 32075
> > >
> > > Enjoy!
> > >
> > > Gareth Horton
> > > Datawatch Corporation
> > >
> >
>
Dee Moore
Datawatch Corporation
Technical Support
978.441.2200
Mark Huston wrote:
> Mark Huston wrote:
>
> I meant to post this before, but I hit "enter" and sent it before I could attach my reply.
>
> I agree that a repository would be valuable. Todd, are you offering to host a web site for the repository? Anyone else?
>
> Gareth, I disagree that editing out the proprietary data is difficult. Sure, you need to modify your reports to scramble proprietary data. Here's what I use to share reports. Open the report in Word. If the layout changes, try changing the font to Courier New or some other non-proportional font. Then use Edit > Replace.
>
> Replace 1, 2, 3, 4, and 5 with 9's, replace 6, 7, 8, and 9 with 1. Then you have all 1's, 9's, and zeroes. You an also replace any company headers, etc. by the same technique. Your model still works but the proprietary information is removed.
>
> I would, however, like to see more examples of calculated fields (hopefully simpler than these). I was struggling with a calculated field that would remove the left two characters in a variable-length string. In VBA, it would look something like =Left(MyField,Len(MyField)-2)) but I couldn't get it to work in Monarch.
>
> My models are relatively simple, and I only use the most basic calculated fields and summary functions. I, too, use Monarch as the "go-between" from reports to Access and Excel. Frankly, I find that it's quicker and more flexible to automate a few processes in Excel to get to the end result than to build a lot of functions into the Monarch summary.
>
> My $0.02,
> Mark!
> > Gareth Horton wrote:
> > > Todd Frisch wrote:
> > > > I would like to make a suggestion to enduser's of Monarch - why don't we create
> > > > a Repository for models, expressions, and examples of our efforts with solutions.
> > > >
> > > > I find Monarch to be one of the most amazing pieces of software I have ever used but it is also
> > > > one of the most difficult to learn as well.
> > > >
> > > > I hope I am not the only user who has captilized on Datawatch's wonderful
> > > > Monarch course but still finds it slow and difficult to recall infrequently used model skills
> > > > to come up with a solution.
> > > >
> > > > I feel I could significantly increase Monarch's use in my position and throught out my
> > > > organization if I could sink my teeth into some advanced examples of how to use this software.
> > > >
> > > > Additionally , if I could see more examples and they had more depth I could move
> > > > away from other pieces of software and focus more on using
> > > > Monarch more extensively. Currently I use monarch primarily as a go between to
> > > > our systems and excel or access.
> > > >
> > > > What do you think - "End User's" and "Datawatch people".
> > > >
> > > > Todd Frisch, Forecast & Sales Analyst, K'nex Industries
> > > > tfrisch@knex.com
> > >
> > > Hi Todd,
> > >
> > > I have thought about this myself before, but the main problem is
> > > the privacy of data. When we look at reports at Datawatch, we make
> > > absolutely sure that customer data is not made available in any way and
> > > often we will sign non-disclosure agreements with customers before looking
> > > at the reports.
> > >
> > > Well, what about "scrubbed" reports, you may ask. The problem here is that
> > > it is incredibly time consuming to do this properly, cutting the report properly to
> > > a sizable length so that it still makes logical sense and then painstakingly replacing
> > > every item of data on the report with bogus data that still makes sense when filtered or
> > > summarised for example.
> > >
> > > Obviously, very basic examples can be mocked up in a text editor, but usually something
> > > really illuminating goes beyond that.
> > >
> > > As regards expressions, you tend to find that many of them are very specific to a particular
> > > task and even to a particular organization, however there are exceptions, fairly generic expressions
> > > which would be useful to list. I have only come across a few myself.
> > >
> > > I will contribute a fairly strange and unique expression I once had to write to strip out double spaces
> > > from text and reduce them back down to single spaces, whilst maintaining the single spaces in the text correctly. It's fairly involved, and I don't think anyone else should be subjected to working it out again. There is doubtless a more elegant way to do it. Just replace <Your Field Here> with the field you need to format.
> > >
> > > Left((Left((ltrim(<Your Field Here>)),(Instr(" ",(ltrim(<Your Field Here>)))))+,$s:LF> If((Instr(" ",(ltrim(<Your Field Here>)))<>0),,$s:LF> (Substr((ltrim(<Your Field Here>)),Instr(" ",(ltrim(<Your Field Here>)))+2)),(ltrim(<Your Field Here>)))),(Instr(" ",(Left((ltrim(<Your Field Here>)),(Instr(" ",(ltrim(<Your Field Here>)))))+,$s:LF> If((Instr(" ",(ltrim(<Your Field Here>)))<>0),,$s:LF> (Substr((ltrim(<Your Field Here>)),Instr(" ",(ltrim(<Your Field Here>)))+2)),(ltrim(<Your Field Here>)))))))+
> > > If((Instr(" ",(Left((ltrim(<Your Field Here>)),(Instr(" ",(ltrim(<Your Field Here>)))))+,$s:LF> If((Instr(" ",(ltrim(<Your Field Here>)))<>0),,$s:LF> (Substr((ltrim(<Your Field Here>)),Instr(" ",(ltrim(<Your Field Here>)))+2)),(ltrim(<Your Field Here>)))))<>0),,$s:LF> > (Substr((Left((ltrim(<Your Field Here>)),(Instr(" ",(ltrim(<Your Field Here>)))))+,$s:LF> If((Instr(" ",(ltrim(<Your Field Here>)))<>0),,$s:LF> (Substr((ltrim(<Your Field Here>)),Instr(" ",(ltrim(<Your Field Here>)))+2)),(ltrim(<Your Field Here>)))),Instr(" ",(Left((ltrim(<Your Field Here>)),(Instr(" ",(ltrim(<Your Field Here>)))))+,$s:LF> If((Instr(" ",(ltrim(<Your Field Here>)))<>0),,$s:LF> (Substr((ltrim(<Your Field Here>)),Instr(" ",(ltrim(<Your Field Here>)))+2)),(ltrim(<Your Field Here>)))))+2)),(Left((ltrim(<Your Field Here>)),(Instr(" ",(ltrim(<Your Field Here>)))))+,$s:LF> If((Instr(" ",(ltrim(<Your Field Here>)))<>0),,$s:LF> (Substr((ltrim(<Your Field Here>)),Instr(" ",(ltrim(<Your Field Here>)))+>> > >
> > > Here's the most accurate conversion from julian to gregorian I know - just replace [Julian_Input] with your
> > > input Julian date field. This is based on the algorithm of Henry F. Fliegel and Thomas C. Van Flandern. The Julian date input must be in numeric format, with no decimal places (denoting time) and the calculated field must be a date format.
> > >
> > > Ctod(Str(Int((Int(80*(Int((Int([Julian_Input])+68569)-Int((146097*(Int((4*(Int([Julian_Input])+68569))/146097))+3)/4)-Int(1461*(Int(4000*((Int([Julian_Input])+68569)-Int((146097*(I nt((4*(Int([Julian_Input])+68569))/146097))+3)/4)+1)/1461001))/4)+31))/2447))+2-12*(Int((Int(80*(Int((Int([Julian_Input])+68569)-Int((146097*(Int((4*(Int([Julian_Input])+68569))/14 6097))+3)/4)-Int(1461*(Int(4000*((Int([Julian_Input])+68569)-Int((146097*(Int((4*(Int([Julian_Input])+68569))/146097))+3)/4)+1)/1461001))/4)+31))/2447))/11))),2,0)+"/"+Str((Int((In t([Julian_Input])+68569)-Int((146097*(Int((4*(Int([Julian_Input])+68569))/146097))+3)/4)-Int(1461*(Int(4000*((Int([Julian_Input])+68569)-Int((146097*(Int((4*(Int([Julian_Input])+68 569))/146097))+3)/4)+1)/1461001))/4)+31))-Int(2447*(Int(80*(Int((Int([Julian_Input])+68569)-Int((146097*(Int((4*(Int([Julian_Input])+68569))/146097))+3)/4)-Int(1461*(Int(4000*((Int ([Julian_Input])+68569)-Int((146097*(Int((4*(Int([Julian_Input])+68569))/146097))+3)/4)+1)/1461001))/>> > >
> > > Int((1461 * (Val(Right([MDYDate],4)) + 4800 + Int((Val(Left([MDYDate],2)) - 14) / 12))) / 4) +
> > > Int((367 * (Val(Left([MDYDate],2)) - 2 - 12 * Int((Val(Left([MDYDate],2)) - 14) / 12))) / 12) -
> > > Int((3 * (Int(Val(Right([MDYDate],4)) + 4900 + Int((Val(Left([MDYDate],2)) - 14) / 12)) / 100)) / 4) + Val(Substr([MDYDate],4,2)) - 32075
> > >
> > > Enjoy!
> > >
> > > Gareth Horton
> > > Datawatch Corporation
> > >
> >
>