String Value manipulation

[AH]

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

[Chanty4u]

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

];

[sunny_talwar · Accepted solution]

May be this

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

[AH]

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

[sunny_talwar]

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?

[AH]

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,

[sunny_talwar]

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

[sasiparupudi1]

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

[AH]

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

Thanks & regards

[sunny_talwar]

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?