topic Re: Using if statement to create field on load in QlikView

Using if statement to create field on load

Thu, 14 Aug 2014 15:05:25 GMT

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?

Re: Using if statement to create field on load

ecolomer — Thu, 14 Aug 2014 15:12:07 GMT

you can use this

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

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

Re: Using if statement to create field on load

maxgro — Thu, 14 Aug 2014 15:26:37 GMT

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;

Re: Using if statement to create field on load

Fri, 15 Aug 2014 11:37:14 GMT

This helped me a lot. Thank you.