How assign calculated value to existing field?

[#1]

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!

[vgutkovsky]

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,

[#3]

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!

[vgutkovsky]

Well, now I'm not sure I understand because doesn't the second table already link to the client? You should be able to use the maxSubsidiary field in charts. But you can always join back into the original table if that's what you mean.

Regards,

[#5]

If I have understood the question correctly, you should be able to use the same expression in the script. Example:
T1:
LOAD Client, SubsidiaryRating FROM Datasource;

T2:
LOAD
Client, max(SubsidiaryRating) AS BestSubsidiary
RESIDENT T2 GROUP BY Client;

This will give you a new table with the same content as the chart with Client and max(SubsidiaryRating)