5 Replies Latest reply: May 15, 2014 9:56 AM by Grant Perkins RSS

    Formula-based Calculated field

    Peter _

      I am having problems trying create a formula to extract data from a field into two separate fields. The one field I have is called Taxes with its corresponding field T_YTD. I want the money figure from T_YTD to be extracted. Each employee has at least one of the two records from the Taxes field that I want to extract, ST1 and ST2. If an employee has both ST1 and ST2, then I want ST2 money to go into a field call Worked-In and ST1 into Lived-In.  If an employee only has ST1, then I want it to go in Worked-In.

      Thanks

      Peter

        • Formula-based Calculated field
          Grant Perkins

          Peter,

           

          I'm thinking I would probably be looking to the LSPLIT and RSPLIT functions BUT really it depends on what the field (does it have to be a single field?) looks like when you have extracted it from the report.

           

          Any chance of posting a sample (made up would be OK) of what it looks like?

           

          If you paste the line or string between in a CODE section (see UBB buttons below the text entry window) it should retain its format.)

           

          Grant

           

            Originally posted by Peter:

          I am having problems trying create a formula to extract data from a field into two separate fields. The one field I have is called Taxes with its corresponding field T_YTD. I want the money figure from T_YTD to be extracted. Each employee has at least one of the two records from the Taxes field that I want to extract, ST1 and ST2. If an employee has both ST1 and ST2, then I want ST2 money to go into a field call Worked-In and ST1 into Lived-In.  If an employee only has ST1, then I want it to go in Worked-In.

          Thanks

          Peter /b[/quote]

           

          [size="1"][ August 06, 2004, 03:58 PM: Message edited by: Grant Perkins ][/size]

          • Formula-based Calculated field
            Peter _

            here is part of the file:

            emp     Code     st     amt

            0014     YST1     NY     3572.02

            0014     YSDI     NJ     0

            0074     YST1     NY     760.31

            0074     YST2     NJ     1208.77

            0074     YLOC     11     0

            0074     YSDI     NJ     0

            0138     YST1     NJ     5686.96

            0138     YSDI     NJ     0

            0213     YST1     NY     1030.46

            0213     YST2     NJ     1201.47

            0213     YSDI     NJ     0

            0579     YST1     NY     2644.77

            0579     YST2     NJ     6589.43

            0579     YSDI     NJ     0

            0588     YST1     NY     1004.8

            0588     YST2     NJ     2896.23

            0588     YLOC     11     0

            0588     YSDI     NJ     0

            0963     YST1     NY     603.29

            0963     YST2     NJ     561.17

            As you can see, not all emp have both YST1 and YST2

            • Formula-based Calculated field
              Grant Perkins

              OK, I see what you need to do.

               

              Is there anything else on the first or second line for each emp which would help to identify where the emp records change? The emp number at the moment is not helpful because ot occurs on every line. I need something that I can use as an append so that the St1 and ST2 fields can both be in a single detail template. If we can't do that it looks like it will get more complicated.

               

              However, if we can find a 'header' per emp record, and given that we want 2 fields and each record will gave at least 2 rows, we can identify a field as ST1 or ST2 using the 'Start Field on' "preceding string" = YST1 for ST1  and YST2 for ST2.

               

              You will need a 2 line sample for the template that starts on the first line of each record. 'Paint' a field, in the first line where the ST values column is. Go to the field properties screen and set the advanced property to field start to be YST1 (the Y looks like it is optional) as a preceding string. Call the field ST1. On the second line do the same thing but start on YST2 and call the field ST2.

               

              Now, as long as you can trap for a whole emp record rather than line by line you will get a single table row with both ST1 and ST2 fields. They will either be populated or one may be empty.

               

              You now need 2 more calculated fields to hold the 'real' values of ST1 and ST2 according to your rule.

               

              So the field Worked-in would be something like

               

              if(ST2>0,ST2,ST1)

               

               

              and LIVED-IN would be

               

              if (ST2>0,ST1,"")

               

              or similar. You may want to see 0 rather than blank.

               

              Now, if you cannot easily identify the whole emp record and can only work with every line as a detail there is a separate problem to solve. And that wold, I think, require a 2 stage process to remove duplicate emp numbers so that emp can be used as a first line identifier for each multi-line record. If you see what I mean.

               

              Let's worry about that later if we need to.

               

              There are other approaches to this problem. However all thiose I can think of so far still require either a distinct way of identifyiing each emp record group by the first of second line OR a 2 stage process where the first stage makes such a unique identification possible.

               

              I hope this helps.

               

              Grant

               

               

              Grant

               

               

                Originally posted by Peter:

              here is part of the file:

              emp     Code     st     amt

              0014     YST1     NY     3572.02

              0014     YSDI     NJ     0

              0074     YST1     NY     760.31

              0074     YST2     NJ     1208.77

              0074     YLOC     11     0

              0074     YSDI     NJ     0

              0138     YST1     NJ     5686.96

              0138     YSDI     NJ     0

              0213     YST1     NY     1030.46

              0213     YST2     NJ     1201.47

              0213     YSDI     NJ     0

              0579     YST1     NY     2644.77

              0579     YST2     NJ     6589.43

              0579     YSDI     NJ     0

              0588     YST1     NY     1004.8

              0588     YST2     NJ     2896.23

              0588     YLOC     11     0

              0588     YSDI     NJ     0

              0963     YST1     NY     603.29

              0963     YST2     NJ     561.17

              As you can see, not all emp have both YST1 and YST2 /b[/quote]

               

              [size="1"][ August 06, 2004, 05:29 PM: Message edited by: Grant Perkins ][/size]

              • Formula-based Calculated field
                Peter _

                This is the way the detail section of my file looks. I have the column with 61 as my qualifier in my detail.  My append, I use as my emp number. The YST2 column, I call it "taxes", the money column I call T_YTD.

                C063A5020002261YFIT 1357724 

                C063A5020002261YSOC 0506294 

                C063A5020002261YMED 0118407 

                C063A5020002261YST1 0187505NY

                C063A5020002261YST2 0301215NJ

                C063A5020002261YLOC1036360811

                C063A5020002261YSDI 0022478NJ

                • Formula-based Calculated field
                  Grant Perkins

                  Peter,

                   

                  I don't suppose that there is any chance that the FIRST line related to any emp ALWAYS starts with a "YFIT" line is there? If it does it should be possible to select all the field based on those lines only

                   

                  We really do need to try to identify a FIRST LINE of each record in order to have one detail selection template.

                   

                  Failing that map the fields you want treating each line as a separate record (as you do now) and create a summary giving the EMP number in one column and your extracted value for ST1 and ST2 in another column AND the value ST1 or ST2 in a third column (plus whatever else you need).

                   

                  In the summary,  for the EMP column only, set the display to NOT display duplicate values.

                   

                  Then EXPORT the result to the fixed width format file making sure all the column widths are as large as they ever need to be before you run the export.

                   

                  That will, in effect, have reformatted the report to provide the single append reference point. You can then use a revised model to pick the detail and append from the emp reference each time it changes, leaving you with both ST1 and ST2 fields in a single record. From THAT point you can apply the formula rules to slot the values into the fields you want - as described previously.

                   

                  Does this make sense? (i.e. can you follow it? It is easier to do than to describe IMHO.)

                   

                  Grant

                   

                   

                  Originally posted by Peter:

                  This is the way the detail section of my file looks. I have the column with 61 as my qualifier in my detail.  My append, I use as my emp number. The YST2 column, I call it "taxes", the money column I call T_YTD.

                  C063A5020002261YFIT 1357724 

                  C063A5020002261YSOC 0506294 

                  C063A5020002261YMED 0118407 

                  C063A5020002261YST1 0187505NY

                  C063A5020002261YST2 0301215NJ

                  C063A5020002261YLOC1036360811

                  C063A5020002261YSDI 0022478NJ /b[/quote]