4 Replies Latest reply: Aug 6, 2010 5:51 AM by Staffan Wessman RSS

    How assign calculated value to existing field?

    Henry Noh

      I have a data table listing CLIENTS, SUBSIDIARY and SUBSIDIARY RATING (best,medium,worst).

      Most Clients have multiple SUBSIDIARIES with multiple RATINGS.

       

      I want to create a new field assigning the best SUBSIDIARY RATING to the CLIENT.

      For example:

      CompanyA has 3 subsidiaries, all rated differently (best,medium,worst). I want to populate a new field with all subsidiaries rated as 'best'.

      What is the best way to do this?

      Thanks in advance!

       

        • How assign calculated value to existing field?
          Vlad Gutkovsky

          You could do something like this:

           


          LOAD
          CLIENT,
          SUBSIDIARY as BestSubsidiary
          RESIDENT mytable
          WHERE [SUBSIDIARY RATING]='best';


          This would create a separate table. You could also reload the table from itself and just add the new "Best Subsidiary" field to the existing table (by using IF during the load instead of WHERE).

          Regards,

            • How assign calculated value to existing field?
              Henry Noh

              Thank you Vlad for you reply.

              I think I'm looking for something different however. To clarify:

              The original data showed this:

              CLIENT SubsidiaryRating

              A 1
              A 3
              B 4
              B 2
              C 5
              C 4
              D 5
              D 2

              1=bad
              2=middle
              3=good
              4=very good
              5=excellent

               

              I created a table showing the rating of the best SUBSIDIARY as follows:

               

              CLIENT max(SubsidiaryRating)

              A 3
              B 4
              C 5
              D 5

              QUESTION: How can I assign the calculated field 'max(SubsidiaryRating)' to the CLIENT. Presumably then I'd also be able to create a chart using the max(SubsididaryRating) field. I hope I explained my question well enough. Thanks in advance!