derive new field from another field

[Anonymous]

i have

id=INS-2014-00001-FIN-00001-SUB-00001

using above field i want to derive below field in script

example:left(INS-2014-00001-FIN-00001-SUB-00001,24) as new_Field

INS-2014-00001-FIN-00001 as new_Field

is there any other way to split (Derive )

[Siva_Sankar]

=left('INS-2014-00001-FIN-00001-SUB-00001',24)

You just ignored to put your text inside single quote. Try the above.

[Anonymous]

thanks for your time.

is there any other way to achieve the same .

i want to derive new field starting to before "SUB"

if i use left(24) in future it might not work

[Siva_Sankar]

=Subfield('INS-2014-00001-FIN-00001-SUB-00001','-SUB')

[PrashantSangle]

Hi,

use subfield()

try like

SubField('INS-2014-00001-FIN-00001-SUB-00001','-SUB') as newField

Regards

[MayilVahanan]

Hi'

Try like this

LOAD *, Mid(id, Index(id, 'SUB')) as newid Inline

[

id

INS-2014-00001-FIN-00001-SUB-00001

];

[Peter_Cammaert · Accepted solution]

This may not look like anything, but unless you specify the exact formatting rules of this value, it will do the job. Let's call your source file MyField:

LOAD ....

     SubField(MyField, '-', 1) & '-' &

     SubField(MyField, '-', 2) & '-' &

     SubField(MyField, '-', 3) & '-' &

     SubField(MyField, '-', 4) & '-' &

     SubField(MyField, '-', 5) AS NewField

FROM ...;

If the SUB text is always present at the same position (and only there), you could also use

LOAD SubField(MyField, '-SUB', 1) AS NewField

FROM ...;

and there are other solutions but they depend on what format these values take.

Best,

Peter

[er_mohit]

Try this

Left('INS-2014-00001-FIN-00001-SUB-00001',index('INS-2014-00001-FIN-00001-SUB-00001','-',5)-1)

[jagan]

Hi,

Try like this

LOAD

*,

Subfield('INS-2014-00001-FIN-00001-SUB-00001','-SUB', 1) AS NewFieldName

FROM DataSource;

Hope this helps you.

Regards,

Jagan.

[Anonymous]

hi

LOAD SubField(MyField, '-SUB', 1) AS NewField

its worked for me .

thanks all