topic Re: String Value manipulation in QlikView

String Value manipulation

AH — Wed, 25 Nov 2020 16:16:04 GMT

I have a situation like this:

INLINE LOAD * [

ABC

MY_NAME_IS_XYZ

I_LIKE_READING_BOOKS_VERY_MUCH

ALTHOUGH_I_LIKE_SPORTS_TOO

];

I would like to have the field value for ABC like this:

ABC

Is Xyz

Very Much

Sports Too

Is there a way to reach to the field value like this?

Thanks & Regards

Re: String Value manipulation

Chanty4u — Mon, 16 Apr 2018 16:41:43 GMT

try this

Table:

load *,

purgechar(Right(ABC, len( ABC ) -Index(ABC,'_', -2)),'_') as LastSecond1;

load * inline [

ABC

MY_NAME_IS_XYZ

I_LIKE_READING_BOOKS_VERY_MUCH

ALTHOUGH_I_LIKE_SPORTS_TOO

];

Re: String Value manipulation

sunny_talwar — Mon, 16 Apr 2018 16:41:48 GMT

May be this

Capitalize(SubField(ABC, '_', -2) & ' ' & SubField(ABC, '_', -1)) as NewABC

Re: String Value manipulation

AH — Mon, 16 Apr 2018 17:07:49 GMT

Hi Sunny,

Your solution looks very good. But if the Value is not just two words i.e kind of variable in nature in that case what would be the solution?

Example value:

ABC

Xyz

Very Much

Like Sports Too

The number of words in the field value Varies here.

Thanks & Regards

Re: String Value manipulation

sunny_talwar — Mon, 16 Apr 2018 17:15:05 GMT

There needs to be some way for us to let QlikView know how many words from the end do we need.... right? What is that logic for you?

Re: String Value manipulation

AH — Mon, 16 Apr 2018 17:43:39 GMT

Hi Sunny,

Thanks for your reply again.

Lets say we have these three values for this particular field and requirements for the three different values are different.

Like,

ABC

MY_NAME_IS_XYZ -------------------> we need to extract only the last word

I_LIKE_READING_BOOKS_VERY_MUCH -------- we need to extract only the last two words

ALTHOUGH_I_LIKE_SPORTS_TOO -----------> we need to extract last three words

i.e,

ABC

Xyz

Very Much

Like Sports Too

Apologies if i creates confusion!

Thanks & regards,

Re: String Value manipulation

sunny_talwar — Mon, 16 Apr 2018 17:46:10 GMT

These are the only three possible values that you field can ever have? I am just trying to understand because if the above is true, then you can do this so easily using Mapping Load or If Statement of Pick Match...

Re: String Value manipulation

sasiparupudi1 — Mon, 16 Apr 2018 18:01:03 GMT

how do you identify which line needs 1 piece and which line needs 3

Re: String Value manipulation

AH — Mon, 16 Apr 2018 18:07:12 GMT

No There are Many values but i just provided a sample.

Thanks & regards

Re: String Value manipulation

sunny_talwar — Mon, 16 Apr 2018 18:08:58 GMT

So, if the fourth line was something like this

I_AM_SUNNY_TALWAR_AND_I_LIKE_QLIKVIEW

how would I know that I need I Like QlikView or Like QlikView or just QlikView?

Re: String Value manipulation

AH — Tue, 17 Apr 2018 00:17:17 GMT

Hi Sunny,

The solution you provided is good. Thanks for your great help and prompt replies!

Regards