Data Load Editor, string manipulation, simple if statement not working..

[Anonymous]

Hi,

I'm trying to manipulate a field value on the fly while it's being loaded into Qlik sense, we can get the value out of the database no problem, we look at the value of the field & if it contains the text "Transferred from", we want to change the entire value of the field to "Vacancy Transfer", otherwise let the field value go through as the original value

So we have tried the following code to help achieve this,

LOAD *,

if(SubStringCount(APPLICANTSOURCE, 'Transferred from') > 0, 'Vacancy Transfer', APPLICANTSOURCE) as APPLICANTSOURCE;

The above results in errors every time, we have tried using it in in different parts of the load script & the error usually pertains to the following line in the script, suggesting issues with the line of code above. We can't seem to figure out why it won't accept this type of if statement.

Do we need to use applyMap instead? it seems to be a bit OTT for what we are trying to achieve.

Any help much appreciated.

[sunny_talwar]

Are you using this as a preceding load or resident load?

LOAD *,

if(SubStringCount(APPLICANTSOURCE, 'Transferred from') > 0, 'Vacancy Transfer', APPLICANTSOURCE) as APPLICANTSOURCE;

[petter · Accepted solution]

The fieldname is not unique within the table. You are using * and includes the field APPLICANTSOURCE AND you are creating a new field named APPLICANTSOURCE so you get the problem with duplicate fieldnames in a single table.

Try to do this:

LOAD *,

if(SubStringCount(APPLICANTSOURCE, 'Transferred from') > 0, 'Vacancy Transfer', APPLICANTSOURCE) as ApplicantSource;

Fieldnames are case sensitive so that will work - I recommend to list the fields you want to keep and not include APPLICANTSOURCE but only using the calculated field....

[Anonymous]

Thank you Petter, that explains it well.