Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
How does it look if you renamed the max-results as MaxLength ?
- Marcus
And which results has MaxLength ?
- Marcus
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;