We want to clean up the values

[Srinivas]

Hi All,

i want to clean up the values, see below screenshot ,

ID	Num	ProNum
123684	125678	13203850
123684	125679	15103910( )(1)(1)
123684	125680	154( )(5)(1)
123685	125681	15103910( )(2)(1)
123685	125682	15103704( )(4)(1)
123686	125683	1510390( )(3)(1)
123686	125684	151704( )(2)(1)
123687	125685	15103910( )(4)(1)

in above screen pronum have different numbers here we need to delete closed  brackets only need before numbers only....I want to see below output way:

ID	Num	ProNum
123684	125678	13203850
123684	125679	15103910
123684	125680	154
123685	125681	15103910
123685	125682	15103704
123686	125683	1510390
123686	125684	151704
123687	125685	15103910

Please help on this.

Advance thanks

Munna

[#2]

Hi Munna,

Subfield(ProNum,'(',1) As ProNum

should give you this result

hope that helps

Joe

[alexandros17]

=Mid(myField, 1, FindOneOf(myField,'()')-1)

[stigchel]

In your load script you can maybe use something like this:

Load....

....

SubField(ProNum,'(',1) as CleanProNum

.......

From.....

[fvelascog72]

Try this:

TMP:

LOAD

*,

SubField(ProNum,'(',1) as "TEST"

INLINE [

ID, Num, ProNum

123684, 125678, 13203850

123684, 125679, 15103910( )(1)(1)

123684, 125680, 154( )(5)(1)

123685, 125681, 15103910( )(2)(1)

123685, 125682, 15103704( )(4)(1)

123686, 125683, 1510390( )(3)(1)

123686, 125684, 151704( )(2)(1)

123687, 125685, 15103910( )(4)(1)

];

[buzzy996]

tab3:

Load * Inline [

ID ,Num ,ProNum

123684, 125678, 13203850

123684, 125679, 15103910( )(1)(1)

123684, 125680, 154( )(5)(1)

123685, 125681, 15103910( )(2)(1)

123685, 125682, 15103704( )(4)(1)

123686, 125683, 1510390( )(3)(1)

123686, 125684, 151704( )(2)(1)

123687, 125685, 15103910( )(4)(1)

];

TAB4:

NoConcatenate Load

ID,

Num,

subfield(ProNum,'(',1) as ProNum

resident tab3;

drop table tab3