Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
If the length of the values is constant, you can try left(), right(), subfield() etc.
Cheers,
Naresh
Use subfield()
subfield(data, ' ', 3) as [Account Number],
subfield(data, ' ', 6) as [Account Name],
subfield(data, ' ', 9) as [Branch Code],
Another possibility.
TextBetween(field, 'Account Number', ' ')
-Rob
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...
subfiled() is best suggestion i believe.
Hi,
This is the way i would normally approach the issues, But i will be giving Rob's approach ago next time.
Mark
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
Hi Rob
The function - TextBetween(field, 'Account Number', ' ')did worked
Thanks
Please go ahead and mark something correct to close the thread.
-Rob