Using if statement to create field on load

[#1]

I am loading a file with a field called "Name". Sample data for this field is below

Name
Fenced DBA
Visitor
Riordian, Rick
Scott, Tate
White, Gan D

Basically on load I want to find values in the Name field that contain people's names (Indicated by a comma in the field value) and split them into first name, last name, and middle initial if relevant. So the fields below would be created based on the one above. If a value is not a person's name, I want the first_name, last_name, and mi to be blank. If it is a person's name, but doesn't have a middle initial, I want to fill in the first_name and last_name fields, but leave the mi field blank.

first_name	last_name	mi
Rick	Riordian
Tate	Scott
Gan	White	D

I think this can be done by using an if statement, but I am not sure how. Any ideas?

[ecolomer]

you can use this

subfield(Name, ',', 1) as last_name

subfield(Name, ',', 2) as first_name

[maxgro · Accepted solution]

source:

load * inline [

Name

"Fenced DBA"

"Visitor"

"Riordian, Rick"

"Scott, Tate"

"White, Gan D"

];

table:

load

  Name,

  last_name,

  if(index(tmp_first_name, ' ')>0, subfield(tmp_first_name,' ',1), tmp_first_name) as first_name,

  if(index(tmp_first_name, ' ')>0, subfield(tmp_first_name,' ',2), null()) as middle_name;

load

  *,

  subfield(Name, ',',1) as last_name,

  trim(subfield(Name, ',',2)) as tmp_first_name

Resident

  source

where index(Name, ',')>0;

DROP Table source;

[#4]

This helped me a lot. Thank you.