How to remove the required data from one field?

[rkpatelqlikview]

For Example

FieldName

This is Dallas - (200)

This is Dallas -(202)

This is Plano 203

I need to remove "This is " and "- (200)" , "(202)" and combine those two Dallas data into one.

Only That City names should be appear. Which function can i use in Qlikview?

Thanks  in advance.

[sunny_talwar]

Does your field always begin with This is and the City is the third word in the string? If not, can you share more sample data to see a pattern?

[antoniotiman]

May be

LOAD SubField(FieldName,' ',3) as FieldName

[rkpatelqlikview]

Yes. Begin with same strings and last of that files is contaning the (202) like this. some records having the '-' also.

Ex:

Fieldname

THIS IS BEAUTY  TEXAS (KL03)

THIS IS BEAUTY  - LONDON_SIGN(KL07)

THIS IS BEAUTY - LONDON (KL04)

THIS IS BEAUTY - LONDON_(KL04)

THIS IS BEAUTY - CANADA (KL14)

THIS IS BEAUTY - LONDON_FUGE(KL07)

These are records in a field.
Data model should be only below records and all that LONDON data should take as only one name. like below:

TEXAS

LONDON 

CANADA

[antoniotiman]

LOAD DISTINCT TextBetween(PurgeChar(Temp,'- '),'#','@') as FieldName;
LOAD Replace(Replace(Replace(FieldName,'THIS IS BEAUTY','#'),'_','@'),'(','@') as Temp
Inline [
FieldName
THIS IS BEAUTY TEXAS (KL03)
THIS IS BEAUTY - LONDON_SIGN(KL07)
THIS IS BEAUTY - LONDON (KL04)
THIS IS BEAUTY - LONDON_(KL04)
THIS IS BEAUTY - CANADA (KL14)
THIS IS BEAUTY - LONDON_FUGE(KL07)
];

[rkpatelqlikview]

Perfect Antonio.

Its working. Thanks

[sunny_talwar]

There are many ways possible.... another one is like this

Table:

LOAD SubField(Trim(SubField(Replace(Replace(Fieldname, '-', ' '), '_', ' '), 'THIS IS BEAUTY ', -1)), ' ', 1) as NewField,

*;

LOAD * INLINE [

    Fieldname

    THIS IS BEAUTY  TEXAS (KL03)

    THIS IS BEAUTY  - LONDON_SIGN(KL07)

    THIS IS BEAUTY - LONDON (KL04)

    THIS IS BEAUTY - LONDON_(KL04)

    THIS IS BEAUTY - CANADA (KL14)

    THIS IS BEAUTY - LONDON_FUGE(KL07)

];

[madhumitha]

just that Sub field position should be 5.

[rkpatelqlikview]

Thanks Sunny.

This is also working well. Thanks for your support.

[oknotsen]

If your question is now answered, please flag the Correct Answer (via the big "Correct Answer" button near every post; not visible in preview) and Helpful Answers (found under the Actions menu under every post).

If not, please make clear what part of this topic you still need help with .