19 Replies Latest reply: May 15, 2014 9:52 AM by Dirk Schulze RSS

    Inconsistencies In Data Output From v6 Pro

    Dirk Schulze

      I've got a weird problem that I hope someone might be able to shed some light on.  I'm using version 6 Pro on a TXT file that is about 22MB in size.  Unfortunately the data I pull from the report isn't always consistent.  The ugly detail follows:

       

      It is a fairly standard report that rarely deviates in it's format.  Each payment received from a borrower is displayed on one line except on the rare occasion that someone pays late charges greater than $999.99, in which case the data is displayed on two lines and is easy to track down.  My model pulls detail from each individual payment, so from this particular report I'm pulling over 71,000 lines of data.  The total amount of payments received in the month for this particular investor appears at the bottom of the report, so I know the total amount collected that I am expecting.  I export the data to an Access DB.  Unfortunately the data that gets dumped into Access isn't always consistent with what is on the report.  I've created a summary in Monarch to see if the problem is Monarch or Access and I've determined it is Monarch.  I've loaded the report and model several times and I've gotten at least three different total payment amounts.  The amount I'm looking for is $56,534,722.03 which I got once.  Another time I got $56,534,712.21, a $9.82 difference.  Another time I got $56,534,510.93, a $211.10.

       

      Is anybody familiar with this problem?  Could I possibly be doing something wrong?  Is 71,000 lines of output too much for v6 Pro to handle?

       

      And before anybody suggests v7, we are planning on purchasing Datapump v6 which is incompatible with v7 (at least the last time I checked).  So v7 isn't really an option at this point.

       

      Any help would be greatly appreciated.  Thanks.

        • Inconsistencies In Data Output From v6 Pro
          Grant Perkins

          Hi skinman,

           

          I have not noticed anything like you describe - but then there have probably been few occasions when I was working with something that had a know total to compare to or, on the other hand, was produced tfrom such a large file (in V6. I have used some similar sized files giving more records in V7).

           

          If you file and model would compress to something reasonable for an email attachment I would be happy to offer to investigate and provide a second opinion/confirmation (or not) for both V6 Pro and V7 Pro. However I am also aware that there may be some confidentiality issues here. If you want to discuss this off line just send me a PM with or without email information according to your preference.

           

          I'm thinking that there may be some OS/Hardware/Windows Settings factors for this so an alternate system might the investigation. Have you already tried that?

           

          Grant

           

          Originally posted by skinman:

          I've got a weird problem that I hope someone might be able to shed some light on.  I'm using version 6 Pro on a TXT file that is about 22MB in size.  Unfortunately the data I pull from the report isn't always consistent.  The ugly detail follows:

           

          It is a fairly standard report that rarely deviates in it's format.  Each payment received from a borrower is displayed on one line except on the rare occasion that someone pays late charges greater than $999.99, in which case the data is displayed on two lines and is easy to track down.  My model pulls detail from each individual payment, so from this particular report I'm pulling over 71,000 lines of data.  The total amount of payments received in the month for this particular investor appears at the bottom of the report, so I know the total amount collected that I am expecting.  I export the data to an Access DB.  Unfortunately the data that gets dumped into Access isn't always consistent with what is on the report.  I've created a summary in Monarch to see if the problem is Monarch or Access and I've determined it is Monarch.  I've loaded the report and model several times and I've gotten at least three different total payment amounts.  The amount I'm looking for is $56,534,722.03 which I got once.  Another time I got $56,534,712.21, a $9.82 difference.  Another time I got $56,534,510.93, a $211.10.

           

          Is anybody familiar with this problem?  Could I possibly be doing something wrong?  Is 71,000 lines of output too much for v6 Pro to handle?

           

          And before anybody suggests v7, we are planning on purchasing Datapump v6 which is incompatible with v7 (at least the last time I checked).  So v7 isn't really an option at this point.

           

          Any help would be greatly appreciated.  Thanks. /b[/quote]

          • Inconsistencies In Data Output From v6 Pro
            Dirk Schulze

            Originally posted by Grant Perkins:

             

            I'm thinking that there may be some OS/Hardware/Windows Settings factors for this so an alternate system might the investigation.[/quote]I'm not sure what you mean by an alternate system.  Something other than Windows?

            • Inconsistencies In Data Output From v6 Pro
              Grant Perkins

              No, just a different windows machine to start with then perhaps one with a different OS level or something.

               

              my post should have read ...

               

              "an alternate system might  HELP[/i]  the investigation"

               

              Just a matter of trying to test whether some of the PC's settings or config are influencing the results.

               

              Monarch for Linux? Interesting thought.

               

              Grant

               

              Originally posted by skinman:

                /size[quote]quote:[/size]Originally posted by Grant Perkins:

               

              I'm thinking that there may be some OS/Hardware/Windows Settings factors for this so an alternate system might the investigation.[/quote]I'm not sure what you mean by an alternate system.  Something other than Windows? /b[/size][/QUOTE]

              • Inconsistencies In Data Output From v6 Pro
                Dirk Schulze

                Oh.  I have tried it on another (newer, more powerful PC) and had the same problem.  I'll have to talk to my office manager about sending you the file, but since it belongs to a client I doubt that is feasible.  I'll let you know.  But I'm getting the impression that either the file and/or the output may be a bit too large for v6 Pro to handle concsistently.  Is that a possibility?

                 

                [size="1"][ April 16, 2004, 10:24 AM: Message edited by: skinman ][/size]

                • Inconsistencies In Data Output From v6 Pro
                  Bruce _

                  Skinman

                  71,000 is easy for Monarch to handle. I used to use ver 5 for 300,000 rows.

                   

                  Just a thought,have you checked to see if the field length is long enough to handle the totals?

                   

                  Is it possible for you to use an edited version of the source file (500 rows), to see what is happening in greater detail?

                   

                  Good luck

                  • Inconsistencies In Data Output From v6 Pro
                    DrsRevenueMan _

                    Hey skinman... did you verify your model?

                    If it's only off 211 then it's probably

                    the integrity of your report/model format that's off.

                     

                    I have worked in monarch since it's inception in the early 90's?.. and most of my differences have been in the model (field widths not big enough..etc.)

                    Try to verify the entire report/model used

                    and make sure no filters are on too.

                     

                    If that isn't working then it could be the mainframe programs.. rounding something when it calculates the total it prints out.

                     

                    But monarch v6 or v7 can easily handel 71,000 rows

                    ..man I have analyzed some huge files with monarch before so capacity is no problem.

                     

                    Good luck.

                    Let me know if you still can't balance.

                    • Inconsistencies In Data Output From v6 Pro
                      Dirk Schulze

                      My field lengths are good.  I verified the field boundries, no problem there.  There aren't any filters in that model.

                       

                      I guess what throws me is that I can open the report and the model and get data that adds up to one amount.  Then close Monarch, then open the exact same report and model again and get data that adds up to a different amount.  So basically I have to keep retrying it until I get the data I'm expecting.  I assume that if it was a problem with the model I would get the exact same incorrect data each and every time.

                      • Inconsistencies In Data Output From v6 Pro
                        Grant Perkins

                        This is what struck me as well.

                         

                        Sounds more like memory - physical or virtual RAM disk - playing up - things overrunning, disk writes not happening before being overwritten and stuff like that.

                         

                        Perhaps odd that you don't get the problem with Access, given the strong connection between Access and Monarch.

                         

                        Might be worth playing with some of the settings for the hardware/OS to see if consistent results can be obtained. But it does sound like one that would be good to bounce off Datawatch Tech Support.

                         

                         

                        Grant

                         

                        Originally posted by skinman:

                        My field lengths are good.  I verified the field boundries, no problem there.  There aren't any filters in that model.

                         

                        I guess what throws me is that I can open the report and the model and get data that adds up to one amount.  Then close Monarch, then open the exact same report and model again and get data that adds up to a different amount.  So basically I have to keep retrying it until I get the data I'm expecting.  I assume that if it was a problem with the model I would get the exact same incorrect data each and every time. /b[/quote]

                        • Inconsistencies In Data Output From v6 Pro
                          Bruce _

                          Just a thought along Grant's thinking, are all the temp directories that Monarch uses big enough & available?

                           

                          Monarch will have to page a 22 meg file & the temp files will probably grow to double that on execution.

                           

                          Other than that I cannot think of a good reason for the inconstancies.

                           

                          Good luck,

                          • Inconsistencies In Data Output From v6 Pro
                            tcorley _

                            Just a quick thought. Look for a null in the field you are trying to total on. Could be a trap or calculated field problem.

                            • Inconsistencies In Data Output From v6 Pro
                              Dirk Schulze

                              It's been awhile since I first posted this topic but I thought I would provide an update.  The inconsistency appears to happen on calculated fields only.  My settings appear to be fine and I'm convinced my model is not the culprit.  We've created some new models that pull summary lines from our reports and will use those to verify the integrity of our numbers.  Not a perfect solution, but it'll work for now.

                               

                              We bring in an IT person on occasion (we have a small office).  I'll pass along everybody's ideas when it becomes appropriate (cost-effective).  Thanks for all your help.

                              • Inconsistencies In Data Output From v6 Pro
                                Mike Urbonas

                                Skinman, I concur with tcorley - make sure there are no Null values in your table. 

                                 

                                As a test, try to do a Summary using the data field(s) you are referencing in your calc fields as your Measures.  If any of the results in the Summary appear blank, it may well mean a Null value exists which is messing up the Summary.

                                 

                                Alternatively, do a Filter on your table using the ISNULL function, similar to this example:

                                 

                                ISNULL("Name of field")=1

                                 

                                and see if any rows still appear.  No rows appearing after such a filter will mean there are no nulls.

                                 

                                One way to remove Null references is to change any nulls to zeroes via a calc field as follows (FieldName would be a numeric field):

                                 

                                If(ISNULL(FieldName)=1,0,FieldName)

                                 

                                Regards,

                                Mike

                                • Inconsistencies In Data Output From v6 Pro
                                  Dirk Schulze

                                  Mike -

                                   

                                  I do have Nulls in my table, I have the view option set to show them as blank.  If the problem occurs again I will try your solution.  Will changing the view option to show 0.00 have the same effect of the calculation you suggested?

                                  • Inconsistencies In Data Output From v6 Pro
                                    Bruce _

                                    Skinman

                                     

                                    Changing this option will have no effect on the calculations, just the display. You will have to make sure that the calculations that depend on the field(s)with nulls are fixed up. Something along the lines of "if(isnull(field),0,field)", to adjust the fields that are actually null into zeros.

                                     

                                    Good luck,

                                    • Inconsistencies In Data Output From v6 Pro
                                      Dirk Schulze

                                      Thanks Bruce.

                                      • Inconsistencies In Data Output From v6 Pro
                                        Grant Perkins

                                        Good advice Bruce.

                                         

                                        The Help has a discussion about the effects of NULLs in a couple of example situations ...

                                         

                                        ... which I have just re-read for the first time in a while and it reminded me that you may need to be careful with simply setting a null value to zero. There may be an effect on calculations being performed and, depending on what they are and what they are for, simply electing to set the value to zero may not always be appropriate.

                                         

                                        I can imagine that there may be times when the NULL value records should be identified and listed separately for auditing and checking purposes (Gathered by a filter) or excluded from the calculation (again by a filter).

                                         

                                        Grant    (being cautious)

                                         

                                         

                                          Originally posted by Bruce:

                                        Skinman

                                         

                                        Changing this option will have no effect on the calculations, just the display. You will have to make sure that the calculations that depend on the field(s)with nulls are fixed up. Something along the lines of "if(isnull(field),0,field)", to adjust the fields that are actually null into zeros.

                                         

                                        Good luck, /b[/quote]

                                         

                                        [size="1"][ June 04, 2004, 02:00 PM: Message edited by: Grant Perkins ][/size]

                                        • Inconsistencies In Data Output From v6 Pro
                                          Dirk Schulze

                                          Originally posted by Mike Urbonas:

                                          If(ISNULL(FieldName)=1,0,FieldName)

                                          /b[/quote]What is the significance of the "=1" part of the above formula?  I read the Help section regarding the ISNULL function but I still don't get it.

                                          • Inconsistencies In Data Output From v6 Pro
                                            Grant Perkins

                                            The ISNULL(Field) function returns a TRUE (1) or FALSE (0) result. So if you create a calculated field with the formula "ISNULL(fieldname)" the result would be 1 or 0 - YES or NO respectively.

                                             

                                            The IF function needs a conditional expression, in this case the condition is

                                            ISNULL(fieldname)=1

                                             

                                            which interprets to 'Yes this is a null field'

                                             

                                            If it does, make the field value zero, otherwise use the field's actual value.

                                             

                                            The formula could be written the other way round.

                                             

                                            IF(ISNULL(Fieldname)=0,[Fieldname],0)

                                             

                                            Which of course interpets to

                                             

                                            If the field is NOT NULL (result of ISNULL is FALSE) use its value, otherwise set the value to zero.

                                             

                                            Does this help at all?

                                             

                                             

                                            Grant

                                             

                                              Originally posted by skinman:

                                               /size[quote]quote:[/size]Originally posted by Mike Urbonas:

                                            If(ISNULL(FieldName)=1,0,FieldName)

                                            /b[/quote]What is the significance of the "=1" part of the above formula?  I read the Help section regarding the ISNULL function but I still don't get it. /b[/size][/QUOTE]Edit: Added some punctuation to make one of the sentences more meaningful. Doh!

                                             

                                            [size="1"][ June 09, 2004, 10:44 AM: Message edited by: Grant Perkins ][/size]

                                            • Inconsistencies In Data Output From v6 Pro
                                              Dirk Schulze

                                              Grant -  Actually that's a great explanation.  Thanks.

                                               

                                               

                                              I've tried to recreate the problem and haven't had any luck.  If I run into it again I'll try the ISNULL solution.  Thanks everybody.