Qlik Community

Ask a Question

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
March 11, 2PM EST: Do More with Qlik - Getting Started wtih Qlik Sense SaaS - Redux. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
davidhobbs
Contributor
Contributor

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

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.

1 Solution

Accepted Solutions
petter
MVP
MVP

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....

View solution in original post

3 Replies
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
MVP
MVP

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....

View solution in original post

davidhobbs
Contributor
Contributor

Thank you Petter, that explains it well.