topic Re: How to remove the required data from one field? in QlikView

How to remove the required data from one field?

rkpatelqlikview — Thu, 16 Nov 2017 17:05:21 GMT

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.

Re: How to remove the required data from one field?

sunny_talwar — Thu, 16 Nov 2017 17:11:55 GMT

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?

Re: How to remove the required data from one field?

antoniotiman — Thu, 16 Nov 2017 17:14:10 GMT

May be

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

Re: How to remove the required data from one field?

rkpatelqlikview — Fri, 17 Nov 2017 06:06:27 GMT

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

Re: How to remove the required data from one field?

antoniotiman — Fri, 17 Nov 2017 10:34:33 GMT

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)
];

Re: How to remove the required data from one field?

rkpatelqlikview — Fri, 17 Nov 2017 11:08:46 GMT

Perfect Antonio.

Its working. Thanks

Re: How to remove the required data from one field?

sunny_talwar — Fri, 17 Nov 2017 12:12:04 GMT

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)

];

Re: How to remove the required data from one field?

madhumitha — Fri, 17 Nov 2017 12:26:16 GMT

just that Sub field position should be 5.

Re: How to remove the required data from one field?

rkpatelqlikview — Fri, 17 Nov 2017 12:32:36 GMT

Thanks Sunny.

This is also working well. Thanks for your support.

Re: How to remove the required data from one field?

oknotsen — Fri, 17 Nov 2017 13:33:16 GMT

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 .

Re: How to remove the required data from one field?

oknotsen — Fri, 17 Nov 2017 13:33:45 GMT

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