5 Replies Latest reply: May 15, 2014 9:54 AM by Winn _ RSS

    Unique Records

    Rusty_Haley _

      I have a report listing menu items (called Sessions) for each of our computer system login accounts.  I have created a table with USER_ID, SESSION_NUMBER and REPORT_DATE.  A unique SESSION_NUMBER (menu item) can appear multiple times in a menu resulting in 2 or more records in a table for that month for that user for that session.  So if a new session was added between last month and this month and shows up on a user's menu twice, the only thing I can see that makes this session in the table unique is the report date. I need to compare last month's report to this month's report to determine if Corporate IT has added or deleted sessions and not told me.  One months report equals 11,000+ records.  Comparing 22,000+ records line by line is no fun. A table would look like this:

       

      [font="courier"]USER_ID          SESSION_NUMBER          REPORT_DATE

      BobS          S1000               10/01/02

      BobS          S1000               11/01/02     (no change)

      BobS          S2000               10/01/02     (deleted session - does not show in Nov report)

      BobS          S3000               11/01/02     (new session - does not show in Oct report)[/font][/quote]I am looking to surface only changes between months.  I have experimented using Count in a Summary but with no luck.  Using V6.01.  Any ideas?

        • Unique Records
          Winn _

          Rusty,

              You are on the right track. Here is a solution to your problem.

           

          First, the REPORT_DATE field should be in Date format.

           

          Second, add the following calculated field:

          Name:DAYS_BACK

          Type:Numeric

          Decimals:0

          Format:General

          Display and Template Width:2

          Formula:Today()-REPORT_DATE

           

          This will give you the difference between the report date and today.

           

          Third, set up a summary as follows:

          Key Fields:

          User_ID     Asc by key    Down   All

          Session_ID  Asc by key    Down   All

           

          Summary Fields:

          DAYS_BACK   Minimum

          DAYS_BACK   Maximum

           

          This will give you how far back the first and last reports were that contained that user and session ID. Based on your example, here is what the summary would look like.

           

          [font="courier"] USER_ID         SESSION_NUMBER   MIN(DAYS_BACK)    MAX(DAYS_BACK)

          Bobs                S1000             14                45

          Bobs                S2000             45                45

          Bobs                S3000             14                14

            /font[/quote]Fourth, Set the coloring on the MIN(DAYS_BACK) and MAX(DAYS_BACK) fields.

           

          If you set the background color of MIN(DAYS_BACK) to red, with a Upper Limit of 30, then you can pick out those Sessions that have been  deleted.

           

          If you set the background color of MAX(DAYS_BACK) to green, with a Lower Limit of 30, then you can pick out those sessions that have been added.

           

          Hope that this works for your needs.

          • Unique Records
            Grant Perkins

            A nice solution from Winn which would work in many situations. Great.

             

            I am wondering why your original solution using count in the summary did not work. As a rough and quick result (probably all you need for your purposes?) it would be the way I would go.

             

            What problem did you have?

             

            Are your reports always run on the first of a month?

             

            Grant

             

            Originally posted by Rusty_Haley:

            I have a report listing menu items (called Sessions) for each of our computer system login accounts.  I have created a table with USER_ID, SESSION_NUMBER and REPORT_DATE.  A unique SESSION_NUMBER (menu item) can appear multiple times in a menu resulting in 2 or more records in a table for that month for that user for that session.  So if a new session was added between last month and this month and shows up on a user's menu twice, the only thing I can see that makes this session in the table unique is the report date. I need to compare last month's report to this month's report to determine if Corporate IT has added or deleted sessions and not told me.  One months report equals 11,000+ records.  Comparing 22,000+ records line by line is no fun. A table would look like this:

             

            [font="courier"]USER_ID          SESSION_NUMBER          REPORT_DATE

            BobS          S1000               10/01/02

            BobS          S1000               11/01/02     (no change)

            BobS          S2000               10/01/02     (deleted session - does not show in Nov report)

            BobS          S3000               11/01/02     (new session - does not show in Oct report)[/font][/quote]I am looking to surface only changes between months.  I have experimented using Count in a Summary but with no luck.  Using V6.01.  Any ideas?[/b][/quote]

            • Unique Records
              Rusty_Haley _

              Thanks Winn!

               

              I have been working hard on this puzzle for a long time and can not believe the solution was this simple.  You have been a big help.  I am curious as to how you arrived at this solution.  Is this a tool you have used for long?  How did this idea come to you?

               

              Grant,

               

              To answer your questions:  The reports are run upon request.  My original solution did not work like I wanted it to as indicated below.

               

              [font="courier"] User Name            Report Date          count

              Alfonso               6/4/2002                32     One change but I still have to hunt it down

              Alfonso               10/18/2002              33

              Andrea                6/4/2002               245     Appears to have no changes but what if one session

              Andrea                10/18/2002             245      was removed and a new one added

              /font[/quote]This is what the new summary looks like now.  This is an account which had been deleted (per my request) and I can now confirm it is gone due to it not showing up on the new report.  You can not see the colors in the below example but the MIN(DAYS_BACK) column is higlighted in RED in the summary.  The 170 in both columns indicate these records are only in the old report from 170 days ago.  The upper limit for MIN(DAYS_BACK) is 60, which is violated by the 170 thus causing this cell in the summary to be highlighted in RED.

               

              [font="courier"] User ID  Session Numbe Session Name                                               MIN(DAYS_BACK) MAX(DAYS_BACK)

              bailbe1o tccom1401m000 Print Customers                                                   RED 170             170

              bailbe1o tccom1402m000 Print Delivery Addresses                                          RED 170             170

              /font[/quote]Below is an example of two sessions that have been added (again, per my request) to this user's menu.  The 34 in the MAX(DAYS_BACK) column is highlighted in BLUE in the summary.  The 34 in both columns indicate these records are only in the new report from 34 days ago.  The lower limit for MAX(DAYS_BACK) is 60, which is violated by the 34 thus causing this cell in the summary to be highlighted in BLUE.

               

              [font="courier"] User ID  Session Numbe Session Name                                               MIN(DAYS_BACK) MAX(DAYS_BACK)

              obertr1w tdsls4110m000 Maintain Sales Orders                                                  34        BLUE 34

              obertr1w tdsls4120m000 Maintain Deliveries                                                    34        BLUE 34

              /font[/quote]To put an even finer point on this new tool, below is an example of how this coloring system can catch the slightest changes.  This is a case where the user is the same, the session number is the same, the only thing that has changed is the title of the session.  In the first record, MAX(DAYS_BACK) is highlighted in BLUE.  In the second record, MIN(DAYS_BACK) is highlighted in RED.

               

              [font="courier"] User ID  Session Numbe Session Name                                               MIN(DAYS_BACK) MAX(DAYS_BACK)

              boswca1o tfgld1206m000 Close Periods                                                          34        BLUE 34

              boswca1o tfgld1206m000 Close Periods Global                                              RED 170            170 /font[/quote]I still have to scroll through 11,000 records but this is made much easier and faster due to the color coding.  I only stop to review the colors which pop up when scrolling down at a fairly fast rate.  I guess the only way this report/process could be improved is to export the summary to a spreadsheet for further processing.  I am happy for now.

               

              Thanks again Winn.

               

              Rusty

              • Unique Records
                Grant Perkins

                Rusty,

                 

                Thanks for detailed explantion. Winn had understood your requirement more completely than I had and provided the comprehensive solution. I had a doubt at the back of my mind as my interpretation of your needs was for a little simpler solution - and I could not see how that would be a full benefit to you in completing the task!

                 

                In terms of scrolling through 11000 lines, have you looked at the possibility of filtering out records that you don't need to assess - those sessions that existed at the start and still exist for example and so are 'no change'. It should be possible to do that either directly based on the calculated fields you already have or by creating another calculated field that, in effect, sets a 'flag' that you can then use as the filter. In fact there may be more than one filter useful to you - perhaps one for Adds and one for Removes and another for Changes?

                 

                You could also export your results and then use that as the Report for another Monarch based process.

                 

                Just a thought.

                 

                Grant

                 

                Originally posted by Rusty_Haley:

                Thanks Winn!

                 

                Grant,

                 

                To answer your questions:  The reports are run upon request.  My original solution did not work like I wanted it to as indicated below.

                I still have to scroll through 11,000 records but this is made much easier and faster due to the color coding.  I only stop to review the colors which pop up when scrolling down at a fairly fast rate.  I guess the only way this report/process could be improved is to export the summary to a spreadsheet for further processing.  I am happy for now.

                • Unique Records
                  Winn _

                  Originally posted by Rusty_Haley:

                  Thanks Winn!

                   

                  I have been working hard on this puzzle for a long time and can not believe the solution was this simple.  You have been a big help.  I am curious as to how you arrived at this solution.  Is this a tool you have used for long?  How did this idea come to you?

                   

                  /b[/quote]Rusty,

                   

                       You're welcome. As far as your questions go, I have used the DAYS_BACK formula in one of my models for several years now. I originally developed for a problem that I was having with a report that I used.

                   

                       My company generates reports that gives inventory levels for our products by warehouse. The reports run each night Monday through Friday without fail. I present Friday night's numbers on a report that I do every Monday. The problem came when we had a holiday that fell on a Monday. When I came to work on Tuesday, I would have Monday night's numbers instead of Friday's. So after talking with one of our programmers, it was decided that the file that I downloaded would be set up to contain the current report plus the previous two. (That was when we used to get both Christmas Eve and Christmas Day off. sigh) SO I developed the DAYS_BACK formula so that I could filter out Friday night's numbers based on the report date.

                   

                       As far as your report goes. You were wanting to know when a series of dates started and ended.

                  If you use DAYS_BACK to convert the the date to a number, then your question becomes,"What are the highest and lowest numbers in this series?" That is where the Minimum and Maximum fuctions come into play.

                   

                  As for your question about how long I have been using Monarch, I consider myself one of the very early adopters. I have used it since it originally came out as Personics Monarch version 1.0 (for DOS).

                   

                  I hope this helps explain things.

                   

                  Winn