How can I turn one field of data into multiple fields ?

[#1]

Hi

One field of my data looks like this.

Account Number 98121012 Account Name XXX Branch Code 63205

All the other fields have just one entry.

I would like to turn this field into 3 different Fields.

Thanks

[NareshGuntur]

If the length of the values is constant, you can try left(), right(), subfield() etc.

Cheers,

Naresh

[Colin-Albert]

Use subfield()

subfield(data, ' ', 3) as [Account Number],

subfield(data, ' ', 6) as [Account Name],

subfield(data, ' ', 9) as [Branch Code],

[rwunderlich · Accepted solution]

Another possibility.

TextBetween(field, 'Account Number', ' ')

-Rob

[Anonymous]

Hello,

also you can use this expressions if you want to split them by the name:

load SubField(SubField(fieldname, 'Account Name', 1 ), 'Account Number', 2 ) as field1,

SubField(SubField(fieldname, 'Branch Code', 1 ), 'Account Name', 2 ) as field2,

SubField(fieldname, 'Branch Code', 2 )as field3

resident...

[buzzy996]

subfiled() is best suggestion i believe.

[Mark_Little]

Hi,

This is the way i would normally approach the issues, But i will be giving Rob's approach ago next time.

Mark

[HirisH_V7]

Hi ,

Check this way according to your field ,

1)

K:

LOAD * INLINE [

Date

20100101

20100102

];

K:

Load

mid(Date,1,4) as YEAR,

Mid(Date,5,6) as Month,

mid(Date,7,8) as Day

Resident K;

2)

K:

LOAD * INLINE [

Date

Jan-2015-P1

Jan-2015-P2

];

K1:

Load

SubField(Date,'-',1) As Month,

SubField(Date,'-',2) As Year,

SubField(Date,'-',3) As Type,

'01-' & If(SubField(Date,'-',1)='Jan','01' , If(SubField(Date,'-',1)='Feb','02' ,If(SubField(Date,'-',1)='Mar','03')))&'-' & SubField(Date,'-',2) As Date

Resident K;

DROP Tables K;

3)

K:

LOAD * INLINE [

Date

Jan-2015-P1

Jan-2015-P2

Jan-2015-A1

];

K1:

Load

TextBetween(Date,'Jan-','-') as Year

Resident K;

DROP Tables K;

Hope this helps,

cheers,

Hirish

[#9]

Hi Rob

The  function - TextBetween(field, 'Account Number', ' ')did worked

Thanks

[rwunderlich]

Please go ahead and mark something correct to close the thread.

-Rob