Clever Trapping to Conditionally Display Append Level Data

New Monarch users quickly learn the concept of Append level templates to mine useful data in addition to Detail level data. For example, it's easy to trap the beginning balance from this sample customer statement within an Append level template (Fig. 1):

However, the Append level data will repeat in each row in the Monarch table (Fig. 2). Typically this presentation of Append data is very useful and desirable, facilitating data filters, calculated fields, etc.

Nevertheless, Monarch users often ask our Technical Support team whether Monarch can be set up to not repeat the Append level data in each row of their table; instead of the table in Fig. 2, they would rather have the data as shown in Fig. 3, with the beginning balance appearing only in the first row of the table, along with the ending balance appearing only in the last row of data for presentation purposes:

This is possible using a different technique to trap our Append level data: Define trap characters that not only mine the desired Append level data, but also intentionally mines data from your Detail data as well. Use the field(s) mined from this Append level template within a calculated field, which will conditionally display the Append level data as you want. Hide all extraneous data fields before exporting your final data. In Fig. 4, we have deleted the Append trap shown in Fig. 1 and created a new Append template. Note the trap characters used in this Append trap intentionally capture additional lines of data. Note also the trap uses a two-line sample and will mine data from the line above the line in which the trap characters are encountered.

This template yields two new fields of data (Fig. 5) for our use:

We can now define a Calculated Field to instruct Monarch to only display the Beginning Balance if the word "Beginning" appears in the field "Desc" (Fig. 6). (Note also we set Monarch to suppress zero values.)

We will use a similar (but slightly trickier) strategy to grab the Ending Balance as well. Instead of trying a Footer template, we can use another Append level template as shown, using an Advanced Field Property: we want Monarch to continue mining the data to the next line of the report, using the End Field On option (Fig. 7):

Note also we defined this field as a Character field. How will the above-defined data field help us isolate the ending balance the way we want? Monarch does not allow the results of a template to overlap one another; so only the last row will actually contain two lines worth of data. This allows us to use a clever little formula (Fig. 8):

Val(TextLine([Desc2 To be Hidden],2)) The TextLine function retrieves only the second line from our Desc2 field. Because only the last row in the Desc2 field contains two lines of data, the above formula yields the ending balance here only. The Val function then converts this to a numeric value. Finally, hide the extraneous columns before exporting your data (we included the words "To be Hidden" in the column names to make this clear). As you can see, trapping your Append level data using these techniques can give you significant new flexibility as to when to display and when to suppress Append level data.

Many thanks to Dee Moore, Technical Support Specialist for Datawatch, for this clever Monarch tip!