To find out maximum length of a field name grouping based on CC. Output should have 2 fields CC, Countryname

[shanmathy]

I have 2 fields, CC and Countryname.

CC, Countryname

22,Netherlands

22,Europe

DD,SOUTH AMERICA

DD,SOUTH AMERICA NOCS

I need to find out the length of the country name field and display the max length field value in Countryname across CC.

Sample output:

CC,Countryname

22,Netherlands

DD,SOUTH AMERICA NOCS;

I tried this. But it didnt work. Please assist on the logic to retrieve the desired output

Load

max(len(countryname)) as Countryname,

CC

from table

group by

CC;

[marcus_sommer]

How does it look if you renamed the max-results as MaxLength ?

- Marcus

[shanmathy]

CC, Country name
22, Netherlands

[marcus_sommer]

And which results has MaxLength ?

- Marcus

[manmadha]

T1:
LOAD * Inline [
CC, Countryname

22,Netherlands

22,Europe

DD,SOUTH AMERICA

DD,SOUTH AMERICA NOCS
]

MAP:
Mapping
Load CC,
Max(Len(Countryname)) AS Max_Len
Resident T1 group by CC;

T2:
Noconcatenate
LOAD CC, Countryname

resident T1 where Len(Countryname)=applymap('MAP',CC,'');

Drop table T1;