Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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
MVP
MVP

Re: Re: Data filtering_1

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

7 Replies

Re: Data filtering_1

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

MVP
MVP

Re: Re: Data filtering_1

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
Contributor III

Re: Data filtering_1

Hi,

Please find the attached application.

I hope it will helpful for you.

Regards

Sathish

Not applicable

Re: Re: Data filtering_1

Thanks Ralf,

In this manner its working now.

Rgds.

MVP
MVP

Re: Data filtering_1

Please mark it as answered then..

Not applicable

Re: Data filtering_1

Marked as Correct answe.

Is that OK? Pls reply or suggest further.

MVP
MVP

Re: Data filtering_1

Thx, this will help others...

Community Browser