Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Data filtering_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.

AB
FUZ-JONA / 00746 / NFUZ007461
HLE-MSC UGANDA / 00334 / SHLE003342
FKA-COPIHUE / 00010 / SFKA000102
FG9-NYK THESEUS / 00027 / EFG9000271
EOW-CMA CGM TOSCA / 01331 / SEOW013312
HTV-VEGA / 00751 / SHTV007512
FJP-AS CATALANIA / 00008 / SFJP000082
FWB-HUDSON RIVER / 00571 / SFWB005712
FWD-HEBE / 00003 / EFWD000031
FFB-RHL AGILITAS / 00744 / NFFB007441
FAR-MSC MADRID / 00334 / SFAR003342

FFR-SANTA CATARINA / 00330 / N

FFR003301
1 Solution

Accepted Solutions
rbecher
MVP
MVP

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

Astrato.io Head of R&D

View solution in original post

7 Replies
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
rbecher
MVP
MVP

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

Astrato.io Head of R&D
sathishkumar_go
Partner - Specialist
Partner - Specialist

Hi,

Please find the attached application.

I hope it will helpful for you.

Regards

Sathish

Not applicable
Author

Thanks Ralf,

In this manner its working now.

Rgds.

rbecher
MVP
MVP

Please mark it as answered then..

Astrato.io Head of R&D
Not applicable
Author

Marked as Correct answe.

Is that OK? Pls reply or suggest further.

rbecher
MVP
MVP

Thx, this will help others...

Astrato.io Head of R&D