Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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
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
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
Hi,
Please find the attached application.
I hope it will helpful for you.
Regards
Sathish
Thanks Ralf,
In this manner its working now.
Rgds.
Please mark it as answered then..
Marked as Correct answe.
Is that OK? Pls reply or suggest further.
Thx, this will help others...