🎉Community Raffle - Win $25

An exclusive raffle opportunity for active members like you! Complete your profile, answer questions and get your first accepted badge to enter the raffle.

Join and Win

not all data in data window

Altair Forum User

hello all, I have a txt doc where I've opened in Monarch 10.5. I have a detail trap and several appends. On the main screen, all the data I want to see is highlighted however, when I go to table view, some of the data does not show up. I looked through the threads I could see that looked like they might apply and I don't have any hidden fields

So, my field looks like:

But my table view only shows the first 3 records then moves on to the next loan record. It doesn't include, for example the 4th loan number down listing min paymnt or principal or interest. I'm new to Monarch and suspect it's likely something very basic I'm not doing correctly, but I'm stuck on identifying it. can anyone point me in the right direction?

thank you all

Paul

Find more posts tagged with

English

Monarch

Accepted answers

All comments

Altair Forum User

If the records all have the same number of lines, I would use a multiple line detail trap and trap all data with just the detail trap. If all records do not have the same lines I would do as you have already done - detail with multiple appends. Since I do not know what you used to trap the data, all I can say is that one or more of the traps is not correct. It is pretty difficult to provide a solution without being able to see the trapping.

Altair Forum User

Hi Paul,

As Joe Berry has already mentioned if the records do not all have the same number of lines things are not quite so straightforward when creating traps for a model.

As we have often seen over the years some people new to Monarch ar unfortunate to be thrown early into a learning curve for some of the more unusual approaches that are required for some report variants - perhaps before they have had the chance to become fully comfortable with regular functionality. I think you may have just joined that number!

No problem and you should still be able to do what you need to do but may have to take a slightly different, perhaps a more advanced, approach.

On order to start form how you see this report can you tell us which lines, in your model, are treated as "Detail" and how you have defined you trap.

I note that you have Monarch 10.5 available so we can work towards something compatible with that.

I agree with Joe that this looks like a multi-row detail template. However working that way with records that have a variable number of lines very likely requires the use of some special facilities that Monarch offers. They are not so easy to describe clearly in a "generic" way but are quite easy to explain when faced with a specific need for a particular report.

To that end the information asked for should provide a good starting point for the explanation.

What can you tell us?

Grant

Altair Forum User

Hi Guys.

Thank you all for your help and direction! Grant, you're exactly correct....thrown into the deep end of the pool with not a lot of training/support on monarch! I've hit this a few different ways but still not getting the result I want.

approaches:

1) I did a detail trap using the first line across...started with the loan number 750XXXXX and across horizontally with all fields. Then I did append traps for the different items (interest, old billed, new billed, tran owed, apr, old billed, new billed, tran owed etc.)

**problem with results: this approach causes items to be skipped an I'm not sure why. for example, it will list my first several instances of the first loan number and

categories, but will skip other items.

2) reversed my order of traps...so, I did a detail trap on one of the sub categories (interest, old billed, new billed, tran owed) then did append traps with the account number line and the other categories.

**problem with results: this appears to have all of my items export, but they're not lined up the way that I need them. that is, all of my apr, interest, other categories are listed, but they're out of order relative to the account number

I've also been working where I do a section at a time but this is time consuming and cumbersome.

What I want to have as out put is

Loan number- acct/error- user id- due date- bill date- segment number-description-old billed-new billed-tran owed

for each description.

One of the difficulties I'm running into is the loan numbers don't always have the same number or type of descriptions and this is making it very difficult for my beginner brain to come up with a solution short of doing a separate model for each description.

any suggestions/direction would be most welcomed!

thank you all for your help and time

Paul

Altair Forum User

Hi Guys.
Thank you all for your help and direction!   Grant, you're exactly correct....thrown into the deep end of the pool with not a lot of training/support on monarch! I've hit this a few different ways but still not getting the result I want.
approaches:
1) I did a detail trap using the first line across...started with the loan number 750XXXXX and across horizontally with all fields. Then I did append traps for the different items (interest, old billed, new billed, tran owed, apr, old billed, new billed, tran owed etc.)
       **problem with results: this approach causes items to be skipped an I'm not sure why. for example, it will list my first several instances of the first loan number and
         categories, but will skip other items.
2) reversed my order of traps...so, I did a detail trap on one of the sub categories (interest, old billed, new billed, tran owed) then did append traps with the account number line and the other categories.
     **problem with results: this appears to have all of my items export, but they're not lined up the way that I need them. that is, all of my apr, interest, other categories are listed, but they're out of order relative to the account number
I've also been working where I do a section at a time but this is time consuming and cumbersome.
What I want to have as out put is
Loan number- acct/error- user id- due date- bill date- segment number-description-old billed-new billed-tran owed
for each description.
One of the difficulties I'm running into is the loan numbers don't always have the same number or type of descriptions and this is making it very difficult for my beginner brain to come up with a solution short of doing a separate model for each description.
any suggestions/direction would be most welcomed!
thank you all for your help and time
Paul

Hi Paul,

Well if it's any consolation your are not the first person to find yourself dumped in the deep end with a report like this! The good news form that is that there are already a number of well used approaches to getting what you need extracted from the report. However such reports can be tricky and quite often throw up a few oddities that only appear for one or two records in many thousand yet cannot be entirely ignored because of the potential effect they may introduce should the anomaly arise.

This implies some considerable checking may be required especially if the expectation is to create a model that can then be deployed frequently on a series of versions of the report during an extended period of use. But that's a production issue for the future. Right now we just need to get you to a position whereby you can extract the data you require.

There are several approaches that are likely to work. Let's look at the basics as they can be seen in the example report posted.

Each record looks like it can be considered as a single record but with a variable number of lines and therefore a variable number of populated data fields.

Therefore I think you were correct to identify the first line as the obvious trap line.

In the sample the shortest report record shows 7 rows in total. If that proves to be true for all of the report (at least 7 rows for any single record) you can set the data sample to be 7 rows long and trap on line 1.

If the first 3 rows are always the same (Min Payment, Principal, Interest) you can map the fields for those rows directly into the template as would be "normal usage".

From the 4th row onwards we have variability - either because the row content is consistently present but not always on the same line of the output in relation to the first line or because some records have additional information lines inserted. To deal with those we can deploy a few special and less obvious techniques and there are several possibilities for the approach.

But for now let's just check that the entire report (and maybe some other report examples, can be successfully mapped using a model that traps on line one, uses a 7 row data sample and can successfully populate all of the fields you need in the first three rows every time with no misses and no anomalies.

Once happy with that we will have some clear guidance for the approach to the next step.

By the way, ,keep the trap as simple as possible - but no simpler!

Loan number, User ID and Seg No look like they must always exist in some form but only on row 1 of a record so try something with those fields but then check the report to make sure the rules are always "true".

For example if Loan Number always starts with a numeric character and is left justified and always in the same character position column then a number trap at that position should prove to be consistent. Maybe the same for User ID and perhaps Seg No always has to be 2 numeric characters?

If that works and gets you consistency for the first 3 rows you have a good starting point.

At this point in will also be useful to check whether the records are in fact a minimum of 7 rows long. (If you scan the report once a & row model has been created any failures are likely to stand out as missed record selections). Also maybe try to ascertain the longest number of lines in a record - that's not critical information (usually) but might help to define which approach to consider for the next stage of model development.

See how you get on with that. I'll go through this in stages rather than hit you with something complex that attempts to cover all of the options that may exist for you!

Grant

Altair Forum User

Hi Paul,

Well if it's any consolation your are not the first person to find yourself dumped in the deep end with a report like this! The good news form that is that there are already a number of well used approaches to getting what you need extracted from the report. However such reports can be tricky and quite often throw up a few oddities that only appear for one or two records in many thousand yet cannot be entirely ignored because of the potential effect they may introduce should the anomaly arise.

This implies some considerable checking may be required especially if the expectation is to create a model that can then be deployed frequently on a series of versions of the report during an extended period of use. But that's a production issue for the future. Right now we just need to get you to a position whereby you can extract the data you require.

There are several approaches that are likely to work. Let's look at the basics as they can be seen in the example report posted.

Each record looks like it can be considered as a single record but with a variable number of lines and therefore a variable number of populated data fields.

Therefore I think you were correct to identify the first line as the obvious trap line.

In the sample the shortest report record shows 7 rows in total. If that proves to be true for all of the report (at least 7 rows for any single record) you can set the data sample to be 7 rows long and trap on line 1.

If the first 3 rows are always the same (Min Payment, Principal, Interest) you can map the fields for those rows directly into the template as would be "normal usage".

From the 4th row onwards we have variability - either because the row content is consistently present but not always on the same line of the output in relation to the first line or because some records have additional information lines inserted. To deal with those we can deploy a few special and less obvious techniques and there are several possibilities for the approach.

But for now let's just check that the entire report (and maybe some other report examples, can be successfully mapped using a model that traps on line one, uses a 7 row data sample and can successfully populate all of the fields you need in the first three rows every time with no misses and no anomalies.

Once happy with that we will have some clear guidance for the approach to the next step.

By the way, ,keep the trap as simple as possible - but no simpler!

Loan number, User ID and Seg No look like they must always exist in some form but only on row 1 of a record so try something with those fields but then check the report to make sure the rules are always "true".

For example if Loan Number always starts with a numeric character and is left justified and always in the same character position column then a number trap at that position should prove to be consistent. Maybe the same for User ID and perhaps Seg No always has to be 2 numeric characters?

If that works and gets you consistency for the first 3 rows you have a good starting point.

At this point in will also be useful to check whether the records are in fact a minimum of 7 rows long. (If you scan the report once a & row model has been created any failures are likely to stand out as missed record selections). Also maybe try to ascertain the longest number of lines in a record - that's not critical information (usually) but might help to define which approach to consider for the next stage of model development.

See how you get on with that. I'll go through this in stages rather than hit you with something complex that attempts to cover all of the options that may exist for you!

Grant

Hi Grant,thank you for the help and direction. Here is my progress and where I'm stuck.

I did a detail trap for the first line but noticed that when there is a page break, it resets the format. this throws my pattern out of whack. I put a page header trap in that isolates the header, but my pattern is still a bit convoluted here. Below is a picture of the page break and detail below and above

I took another run at it and did multi line detail trap and picked up the loan number, acct/err.user id, due date, bill date and segment number. then I did an append trap for the different categories. the problem I run into is when I get to the loan number ending in 02, it looks like the last interest line for the account ending in 00 populates in my 02 line (below)

This isn't a report I'll need to do on a very regular basis...it's part of a balancing process I'm using for a migration so my solution doesn't need to be very elegant I'll likely need the data 3 more times after this.

so, for the first part of my output "Loan number- acct/error- user id- due date- bill date- segment number"-this looks like it's pulling over correctly. I get sideways on the second part "description-old billed-new billed-tran owed" I think largely because the descriptions and the line numbers aren't always the same (6 lines, 7 lines, etc.)

what do you think would be good next steps?

thank you again very much for your help!!!!!

Paul

Altair Forum User

Hi Grant,thank you for the help and direction. Here is my progress and where I'm stuck.
I did a detail trap for the first line but noticed that when there is a page break, it resets the format. this throws my pattern out of whack. I put a page header trap in that isolates the header, but my pattern is still a bit convoluted here. Below is a picture of the page break and detail below and above
I took another run at it and did multi line detail trap and picked up the loan number, acct/err.user id, due date, bill date and segment number. then I did an append trap for the different categories. the problem I run into is when I get to the loan number ending in 02, it looks like the last interest line for the account ending in 00 populates in my 02 line (below)

This isn't a report I'll need to do on a very regular basis...it's part of a balancing process I'm using for a migration so my solution doesn't need to be very elegant I'll likely need the data 3 more times after this.
so, for the first part of my output "Loan number- acct/error- user id- due date- bill date- segment number"-this looks like it's pulling over correctly. I get sideways on the second part "description-old billed-new billed-tran owed" I think largely because the descriptions and the line numbers aren't always the same (6 lines, 7 lines, etc.)
what do you think would be good next steps?
thank you again very much for your help!!!!!
Paul

Hi Paul,

Well, that's an interesting variation that one rarely sees.

Other types of formatting that upset page breaks and beat the designed functionality of the Page Header template are quite common but this one less so.

OK, plan C.

Sticking with the first row of the multi-line record concept, can you define a trap on that line that does NOT use the fields I suggested before but still provides a unique trap opportunity that will only capture the first data line or the record?

We can add the first 5 columns as an append template - that they are repeated within a record that crosses a page break should not matter.

So, for example, is the first row of the record ALWAYS "MIN PAYMNT"? in the Description column?

If so try using that as the trap to see what that gives you. However also create a Page Header template to make the page header lines "invisible" to the routine that finds the Detail templates in order to make the visual checking of the results somewhat easier.

Let's see what that gives you.

Grant

ETA.

Also bear in mind that APPEND traps need to relate to information ABOVE (or BEFORE) your DETAIL trap. (But when the time comes I can advise you of a way to make appends functional in many situations where the data to be extracted sit after the detail trap.)

Altair Forum User

Hi Paul,

Well, that's an interesting variation that one rarely sees.

Other types of formatting that upset page breaks and beat the designed functionality of the Page Header template are quite common but this one less so.

OK, plan C.

Sticking with the first row of the multi-line record concept, can you define a trap on that line that does NOT use the fields I suggested before but still provides a unique trap opportunity that will only capture the first data line or the record?

We can add the first 5 columns as an append template - that they are repeated within a record that crosses a page break should not matter.

So, for example, is the first row of the record ALWAYS "MIN PAYMNT"? in the Description column?

If so try using that as the trap to see what that gives you. However also create a Page Header template to make the page header lines "invisible" to the routine that finds the Detail templates in order to make the visual checking of the results somewhat easier.

Let's see what that gives you.

Grant

ETA.

Also bear in mind that APPEND traps need to relate to information ABOVE (or BEFORE) your DETAIL trap. (But when the time comes I can advise you of a way to make appends functional in many situations where the data to be extracted sit after the detail trap.)

Another thought.

Another thought, if you can trap using "MIN PAYMNT" you can then simply defines the fields in the first 5 columns as part of the main record.

All being well the later repeat over a page break will be ignored.

But should there be some as yet unidentified reason for that not to work we have other options.

Grant