Data filtering_1

[#1]

Hi Experts,

Have data in below manner as shown in Col: "A", need the resul as shown in Col: "B", where last Alphabets after second slash "/" denotes

N for 1

S for 2

E for 3

pls need your expert advice.

A	B
FUZ-JONA / 00746 / N	FUZ007461
HLE-MSC UGANDA / 00334 / S	HLE003342
FKA-COPIHUE / 00010 / S	FKA000102
FG9-NYK THESEUS / 00027 / E	FG9000271
EOW-CMA CGM TOSCA / 01331 / S	EOW013312
HTV-VEGA / 00751 / S	HTV007512
FJP-AS CATALANIA / 00008 / S	FJP000082
FWB-HUDSON RIVER / 00571 / S	FWB005712
FWD-HEBE / 00003 / E	FWD000031
FFB-RHL AGILITAS / 00744 / N	FFB007441
FAR-MSC MADRID / 00334 / S	FAR003342
FFR-SANTA CATARINA / 00330 / N	FFR003301

[kaushiknsolanki]

HI,

     Try this,

DATA:

LOAD SubField(A,'-',1)as PART1,SubField(A,'/',2)as PART2,SubField(A,'/',3) as PART3,A INLINE [

A

FUZ-JONA / 00746 / N

HLE-MSC UGANDA / 00334 / S

FKA-COPIHUE / 00010 / S

FG9-NYK THESEUS / 00027 / E

EOW-CMA CGM TOSCA / 01331 / S

HTV-VEGA / 00751 / S

FJP-AS CATALANIA / 00008 / S

FWB-HUDSON RIVER / 00571 / S

FWD-HEBE / 00003 / E

FFB-RHL AGILITAS / 00744 / N

FAR-MSC MADRID / 00334 / S

FFR-SANTA CATARINA / 00330 / N

];

left join

Load * inline [

PART3,MAP

S,2

N,1

E,3

];

FINALDATA:

Load A,PART1&PART2&MAP as NewField Resident DATA;

Drop table DATA;

Regards,

Kaushik Solanki

[rbecher · Accepted solution]

Following Kaushik's proposal I would rather use a map to omit the join and have all done into one LOAD:

MapPart:
Mapping Load * inline [
PART3,MAP
S,2
N,1
E,3
];

DATA:
LOAD Trim(SubField(A,'-',1)) & Trim(SubField(A,'/',2)) & ApplyMap('MapPart',Trim(SubField(A,'/',3))) as NewField 
INLINE [
A
FUZ-JONA / 00746 / N
HLE-MSC UGANDA / 00334 / S
FKA-COPIHUE / 00010 / S
FG9-NYK THESEUS / 00027 / E
EOW-CMA CGM TOSCA / 01331 / S
HTV-VEGA / 00751 / S
FJP-AS CATALANIA / 00008 / S
FWB-HUDSON RIVER / 00571 / S
FWD-HEBE / 00003 / E
FFB-RHL AGILITAS / 00744 / N
FAR-MSC MADRID / 00334 / S
FFR-SANTA CATARINA / 00330 / N
];

- Ralf

[sathishkumar_go]

Hi,

Please find the attached application.

I hope it will helpful for you.

Regards

Sathish

[#5]

Thanks Ralf,

In this manner its working now.

Rgds.

[rbecher]

Please mark it as answered then..

[#7]

Marked as Correct answe.

Is that OK? Pls reply or suggest further.

[rbecher]

Thx, this will help others...