Trimming Data

[mccook]

Hi,

I have data like below being loaded into a column called Name:

Row 1

8932h3*(&"*29Default*&38 agg\Dean McCook               8932h3*(&"*29Default*&38 agg\Tim Saddler

Row 2

8932h3*(&"*29Default*&38 agg\Gavin Kite

The desired result I want is:

Name:

Default*&38 agg\Gavin Kite

29Default*&38 agg\Tim Saddler

Which I will then tidy up to be (I'm ok with this bit)

Name:

Gavin Kite

Tim Saddler

My current result is:

Dean McCook

Tim Saddler

Gavin Kite

As I don't know a way of trimming row 1 to remove the first part, the logic is keep anything to the right of the last instance of Default in the string, any help would be appreciated.

Thanks,

Dean

[tresesco]

Try:

Load

          SubField(Name, '/' , -1)    // -1 refers the last substring

[#3]

What about this?:

tab:

LOAD rowno() as id,* INLINE [

    F1

    '8932h3*(&"*29Default*&38 agg\Dean McCook               8932h3*(&"*29Default*&38 agg\Tim Saddler'

    '8932h3*(&"*29Default*&38 agg\Gavin Kite'

];

tab1:

load RowNo() as sub_id, id, ltrim(SubField(F1,'8932h3*(&"*29Default*&38 agg\')) as name Resident tab;

join(tab1)

load id,max(sub_id) as sub_id,1 as key Resident tab1 Group by id;

tab2:

load name Resident tab1 where key=1;

drop tables tab, tab1; 

[mccook]

Thanks,

Sorry, I was a little lazy with the rows (copying and pasting), they have different characters and numbers and sometimes there can be more than one name e.g.

Row 1

asfdah3*(&"*19Default*&38 agg\Dean McCook               893ada3*(&"*29Default*&38 agg\Tim Saddler

Row 2

8adfas2h3*(&aaad9Default*&38 agg\Gavin Kite

Row 3

8dfas2h3*(&aaad9Default*&38 agg\Harp Sandu            8aada3*(&"*29Default*&38 agg\Kev Smith    agg\Mark Lees

So anytime I see the last instance of the word Default, the name or names after that I want, so in this new example the answer I need is:

Name:

Tim Saddler

Gavin Kite

Kev Smith

Mark Lees

Cheers,

Dean

[mccook]

If I can remove/trim the fields of anything before the last instance of default, I can already do the rest.

Cheers,

Dean

[#6]

Dean,

you still may cut it once or maybe twice with SubField() and join with max ...

regards

Darek

[mccook]

Thanks,

Not sure if this is what you mean, its not giving me the desired results?

LOAD * INLINE [

    Name

    'asfdah3*(&"*19Default*&38 agg\Dean McCook               893ada3*(&"*29Default*&38 agg\Tim Saddler'

    '8adfas2h3*(&aaad9Default*&38 agg\Gavin Kite'

'8dfas2h3*(&aaad9Default*&38 agg\Harp Sandu            8aada3*(&"*29Default*&38 agg\Kev Smith    agg\Mark Lees'

];

test2:

NoConcatenate

Load

SubField(Name, '/' , -1) as Name

Resident test1;

drop table test1;

[#8 · Accepted solution]

after last default:

tab:

LOAD rowno() as id, * INLINE [

    F1

    'asfdah3*(&"*19Default*&38 agg\Dean McCook               893ada3*(&"*29Default*&38 agg\Tim Saddler'

    8adfas2h3*(&aaad9Default*&38 agg\Gavin Kite

    '8dfas2h3*(&aaad9Default*&38 agg\Harp Sandu            8aada3*(&"*29Default*&38 agg\Kev Smith    agg\Mark Lees'

];

tab1:

load RowNo() as sub_id, id, ltrim(SubField(F1,'Default')) as name Resident tab;

join(tab1)

load id,max(sub_id) as sub_id,1 as key Resident tab1 Group by id;

tab2:

load name Resident tab1 where key=1;

drop tables tab, tab1; 

[its_anandrjs]

Try with this load script by use of the subfield

Sufield:

LOAD

SubField((SubField(Row,'    ',-1)),'\',2) as FinalString,

Row;

LOAD * Inline

[

Row

8932h3*(&"*29Default*&38 agg\Dean McCook               8932h3*(&"*29Default*&38 agg\Tim Saddler

8932h3*(&"*29Default*&38 agg\Gavin Kite

];

This will give out out

FinalString

Gavin Kite

Tim Saddler

[its_anandrjs]

Try this in your load script will give as your result please check.

SubField((SubField(Row,'    ',-1)),'\',2) as FinalString,