4 Replies Latest reply: May 15, 2014 10:12 AM by a2z143 _ RSS

    Summing up

    a2z143 _

      I need help in summing up Soucre B and E Amt and placing in Amt1 and placing Source B in Amt2 for each SSN

       

      SSN_________Source____Amt________Amt1_______Amt2

      XXXXX6789_____B______$100.00_____$200.00____$100.00

      XXXXX6789_____D______$100.00______________________

      XXXXX6789_____E______$100.00______________________

      XXXXX6790_____B______$500.00____$1000.00____$500.00

      XXXXX6790_____D______$500.00______________________

      XXXXX6790_____E______$500.00______________________

       

      Thanks

        • Summing up
          kbelli _

          Hi a2z,

           

          I hope I'm understanding what you're asking. 

           

          Could you just create two new calculated fields Rpt_Amt1 and Rpt_Amt2 and define them as:

           

          Rpt_Amt1 = if(.in.("B","E"),[Amt]+,1/0)

          Rpt_Amt2 = if(.in.("B"),[Amt]+,1/0)

           

          and then pull everything into a summary with SSN as a key field and sum() and sum(Rpt_Amt2]) as measures?

           

          That should give you something like:

           

          SSN_________Rpt_Amt1___Rpt_Amt2

          XXXXX6789____$400.00____$200.0 0

          XXXXX6790___$2000.00___$1000.0 0

           

           

          I'm not sure if that's what you were getting at.  Could you show what you'd like the data to wind up looking like?

            • Summing up
              a2z143 _

              kbelli,

               

              This is how the data comes in:

               

              SSN_________Source____Amt___

              XXXXX6789_____B______$100.00

              XXXXX6789_____D______$100.00

              XXXXX6789_____E______$100.00

              XXXXX6790_____B______$500.00

              XXXXX6790_____D______$500.00

              XXXXX6790_____E______$500.00

               

              this is the expected results:

               

              SSN_________Rpt_Amt1___Rpt_Amt2

              XXXXX6789____$200.00____$100.00

              XXXXX6790___$1000.00____$500.00

               

              Hope this helps, thanks for your help.

                • Summing up
                  Grant Perkins

                  There are a couple of approaches to this that work using an easy 2 stage process. What you need is an approach that makes each SSN a single record after complete extraction to the table. From there a couple of calculated fields would be all that is required in the second stage of the process.

                   

                  So you could either set up a first simple model that reads the incoming data and simply outputs the columns to a new file but skipping duplicate entries for the SSN column. That makes it easy to make each SSN a single multi-row record form which a suitable table can be created.

                   

                  The new output would look like this;

                  SSN_________Source____Amt___

                  XXXXX6789_____B______$100.00

                                D______$100.00

                                E______$100.00

                  XXXXX6790_____B______$500.00

                                D______$500.00

                                E______$500.00

                   

                      /code

                   

                  Alternatively use a Summary for the same effect making the "Source" field an 'across' field to turn it into columns by creating fields that specifically pick just the B or D or E data. Then export the Summary (which should now have one row per SSN) and use that exported file with a simple second model to provide the source data for the final output required.

                   

                  If this is to be a regular task a quick batch script (or program if you cut code) can automate it for you.

                   

                  HTH.

                   

                   

                  Grant