Creating field on the load statement from another field

[Mt3]

Hi,

I am aiming into creating subfield "Aimed" from field "Current" on the load statement, as per below picture. 

Current	Aimed	Aimed2
A - Text	A	A - Text
Acd - Text1	Acd	Acd - Text1
A - Text2	A	A - Text2
Bb - Text1	Bb	Bb - Text1
Ba - Text2	Ba	Ba - Text2
B - Text3	B	B - Text3

Are there any tool that could support me with that? First thing that comes to my mind is:

-separating value that comes before space or '-'

- matching if value is equal to a, b, etc.

EDIT: If anybody will look after an answer, i just did separation with SubField Function, it works very well:

FullName:
LOAD * inline [ Name 'Dave Owen' 'Joe Tem' ];
SepNames: Load Name, SubField(Name, ' ',1) as FirstName, SubField(Name, ' ',-1) as Surname
Resident FullName; Drop Table FullName

[Andrei_Cusnir · Accepted solution]

Hello, 

If my understanding is correct, you were able to resolve the issue with the use of SubField() function. Is that right?

The complete solution for your use case scenario is:

//Load the table

FullName:
LOAD * inline [ 

    Name 

   'Dave Owen' 

   'Joe Tem'

];

//Load name and surname separately into another table:
SepNames: 

Load 

   Name, 

   SubField(Name, ' ',1) as FirstName, 

   SubField(Name, ' ',-1) as Surname
Resident FullName; 

//Drop the temp table:

Drop Table FullName;

In that case, you are right. If you need to separate an string value like "Name Surname" then you have to do as you already did, by using the function SubField(). First argument is your string value, second argument is the delimiter (which in this case is the space " ") and the last argument will give you the part of the split text. So the after math would be an array that looks like ['Name', 'Surname'], so using the number 1 as argument will give you "Name" which is the first element and using the number 2 will give you "Surname" which is the second element. However, using the argument -1 as you did will give you the last element, which in this case is the Surname.

If the above description is the complete solution to your issue, please mark it as Accepted Solution to give further visibility to other community members.