5 Replies Latest reply: May 15, 2014 9:58 AM by Sikle1969 _ RSS

    Calculated Field help

    Sikle1969 _

      Hello,

       

      I have a report containing claim forms with a random number of records. For my example lets say 5. 

       

      I want to change a field, say "type", when a code = x. 

       

      So If(code = x,type = yy,type) changes just the ONE record.  I am trying to get it to change "type" for all 5 records on that page.  The code could come in any of the five records randomly.  So if the code were to hit in record 3 how can I change not only record 3 but 1,2,4,5 on that page?

       

      I'm trying to think of a way to use the page number.  Thanks for any help.

       

      CPK

        • Calculated Field help
          Tom Whiteside

          Hi, CPK!

           

          I remember you from last year.  Question - - what is unique about each page that having a Code = x changes the type field for only those records on that single page?  That is, what is it about a single page that a Code = x does not change two, three, or even four pages?

           

          What I'm getting at is similar to your own approach of looking at page numbers, but I'm looking for something that could be used as a different append field for each page?

           

          As always, if it's simpler to send me the report, your data will be kept confidential and the report sent back to you with the model.

          • Calculated Field help
            Steve Caiels

            Hi,

             

            I think a two stage approach and ideally Monarch Pro will be needed here.

             

            Stage 1 would be to create a table containing at least page number and just the ones that match your criteria. 

             

            [font="courier"]page     type

            1        x

            1        AA

            1        x

            2        w

            2        x

            3        x

            3        AA

            3        x[/font][/quote]becomes

            [font="courier"]Page     type

            1        AA

            3        AA[/font][/quote]Then export this as a database, and join it back into the table using page number as a key and then tidy the result with a calculated field of

            IF(IsNull(joined),type,joined)

             

            [font="courier"]page     type    joined   Calculated

            1        x       AA       AA

            1        AA      AA       AA

            1        x       AA       AA

            2        w       (null)   w

            2        x       (null)   x

            3        x       AA       AA

            3        AA      AA       AA

            3        x       AA       AA[/font][/quote]It would be possible with V6 or V7 Standard, but you’d have to manually copy and paste the results of the filter into a calculated lookup field rather than use the database join.  With Pro, the whole task could be automated using batch files, OLE or the new project exports in V7.   Datapump will give time and/or trigger based scheduling (and now I’ll put my sales hat away!)

             

             

            Cheers

            Steve

            • Calculated Field help
              Grant Perkins

              Hi CPK,

               

              Here is a method that will work if your report is very structured. It may also be page independent.

               

              Assuming you have something that looks a little like this (apologies, have played with spacing and inserting as code but still can't quite get the columnar layout I intended! I'm sure you can work out what I mean for the 3 columns)

               

              Page 1

              Header :   1aaaaaaaaaaaaa

              Line ID     CODE     Data

              Line 1     aa     H1L111111111

              Line 2     bb     H1L222222222

              Line 3     cc     H1L333333333

              Line 4     dd     H1L444444444

              Line 5     XX     H1L555555555

               

              Page 2

              Header :   2bbbbbbbbbbbb

               

              Line 1     aa     H2L111111111

              Line 2     bb     H2L222222222

              Line 3     cc     H2L333333333

              Line 4     dd     H2L444444444

              Line 5     99     H2L555555555

               

              Page 3

              Header :   3cccccccccccccc

               

              Line 1     aa     H3L111111111

              Line 2     bb     H3L222222222

              Line 3     cc     H3L333333333

              Line 4     dd     H3L444444444

               

               

              Page 4

              Header :   4dddddddddddd

               

              Line 1     aa     H4L111111111

              Line 2     bb     H4L222222222

              Line 3     cc     H4L333333333

              Line 4     dd     H4L444444444

              Line 5     ee     H4L555555555

               

              Page 5

              Header :   5eeeeeeeeeeee

               

              Line 1     55     H5L111111111

               

               

              Page 6

               

              etc

               

              The following should work.

               

              "Detail" lines trapped on 'Line'.

               

              "Append 1" trapped on something in the Header line (e.g. 'Head') and highlight the data from the line.

               

              "Append 2" also trapped on some of the header line text but for as many rows as the maximum you would find from (and including)

              the header line to the last possible detail line. I.E. if your report may sometimes have 20 detail lines, go from the header line to the 20th detail line when defining the template.

               

              On the first row of the multi line  template define a field (1 char may do ) which will be populated (E.G there will be data at that position HORIZONTALLY in the row) if your target code(s) appears on the row. What the data is probably does not matter. This method simply uses it as a proxy flag. Make sure the field is positioned TO THE RIGHT of your target code column.

               

              For the field select Field Definition>>OPTIONS and set the 'Start Field On' value to 'Preceding String' and enter the value of the Special CODE that you wish to identify, Set the End Field On to 1 line or Minimum action. Note that the Special Code really needs to be something that cannot appear under any other circumstances to the LEFT of your field. Define the preceding string with SPACES either side of the code if necessary. You may need to experiment, it all depened on the codes you have to check for.

               

              If you then go to the table you should see the detail; lines as you have defined them plus, for each detail set under a header or 'Page' an append field that will either have data in it or be blank. If it is blank the line codes apply, if not your override code should apply. A simple IF statement to populate a calculated field with either the line code OR the code related to a populated append field should give the code you want to see for all of the records.

               

              If there are several codes you wish to look for in the same way you can do that as well (up to the maximum number of 'lines' you allowed in the template). Simply define a field in the same horizontal position in the append tamplate but on the next free row. Use the options to set the same definition but change the 'Preceding string' to whatever other code you wish to identify. You will end up with multiple fields (naming each according to the code it identifies might be useful). This makes the IF statement a little more complex but still possible. Alternatively a simple calculated field concatenating all the possible fields might work just as well.

               

              If there is a possibility that a single 'page' might contain more than one of the special codes  ...  then life gets really exciting! If one takes precedence you would clearly be looking at some sort of nested IF statement OR cocatenating the values and then analysing the resultant field to assess which code to apply. At least you would be able to see all the possibilities from the data available.

               

              Of course there is always the great possibility that your report format makes this impossible to apply - in which case I would go for Steve's solution though it looked like Tom was asking an interesting question and may have a neat trick in his armoury somewhere. At time of writing I have not rechecked for new posts. Certainly my route has some limitations but, under optimal circumstances and for the right sort of report, it can work satisfactorily and in a single pass.

               

              Happy to provide more information if you get stuck on anything.

               

              Good luck,

               

              Grant

               

               

                 Originally posted by Sikle1969:

              Hello,

               

              I have a report containing claim forms with a random number of records. For my example lets say 5. 

               

              I want to change a field, say "type", when a code = x. 

               

              So If(code = x,type = yy,type) changes just the ONE record.  I am trying to get it to change "type" for all 5 records on that page.  The code could come in any of the five records randomly.  So if the code were to hit in record 3 how can I change not only record 3 but 1,2,4,5 on that page?

               

              I'm trying to think of a way to use the page number.  Thanks for any help.

               

              CPK /b[/quote]

               

              [size="1"][ July 18, 2003, 03:06 PM: Message edited by: Grant Perkins ][/size]

              • Calculated Field help
                Tom Whiteside

                Both of my highly esteemed    :cool:    and highly able       colleagues from across the pond seem to have covered effective solutions to your problem, CPK.  You have your choice   :confused:   of two excellent options!

                 

                Grant's approach already assumes the existence of page header information similar to for what I was asking you.  If a page related append field or header already exists, go ahead and use his one-pass method.  Although Grant flatters me, there is no need to even look in my armory - - it's contents are barely suited for small militia.

                 

                Steve's approach is both simple and elegant, building on your original idea of using the page number.  It is the best solution if there are no page specific append fields or page specific headers.

                 

                Let us know how this turns out.  I'm still interested in hearing if your report has any page specific append or header information - - my armory has lots of empty space to fill.   :rolleyes:

                • Calculated Field help
                  Sikle1969 _

                  Thanks to all.  Haven't made time to try your suggestions, but will follow up when I do. tgif