Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Inserting field with an if statement

Hi everyone,

I have imported a few SQL database tables into my qlikview worksheet, but I am interested in creating a new field on this import and can't seem to get it to work.

This is what the non-working code looks like:

TableName:

SQL SELECT

"Field1",

"Field2",

if(Field1 < 30, 'current'),

if(Field1 > 29 AND Field 1 <60, '30-59'),

if(Field1 > 59 AND Field1 < 90, '60-89'),

if(Field1 > 89 AND Field1 <120, '90-119'),

if(Field1 > 119, '120+') as [Description]


FROM SQL table name;


If you can't tell from the code I am trying to use logic based on field1 to insert specific strings into a new field called Description. Does anyone know what I am doing wrong?


Thank you

11 Replies
MK_QSL
MVP
MVP

if(Field1 < 30, 'current',

if(Field1 > 29 AND Field 1 <60, '30-59',

if(Field1 > 59 AND Field1 < 90, '60-89',

if(Field1 > 89 AND Field1 <120, '90-119',

if(Field1 > 119, '120+'))))) as [Description]

alexandros17
Partner - Champion III
Partner - Champion III

Quite good, just balance parenthesis:

Try this

if(Field1 < 30, 'current',

if(Field1 > 29 AND Field 1 <60, '30-59',

if(Field1 > 59 AND Field1 < 90, '60-89',

if(Field1 > 89 AND Field1 <120, '90-119',

if(Field1 > 119, '120+', 'N.A.')

))))

as [Description]

Not applicable
Author

I fixed the code to look like that, but for some reason I am still getting a syntax error. Should I be putting this at the bottom of the script or inside of the SQL SELECT statements?

alexandros17
Partner - Champion III
Partner - Champion III

SELECT

"Field1",

"Field2",

if(Field1 < 30, 'current',

if(Field1 > 29 AND Field 1 <60, '30-59',

if(Field1 > 59 AND Field1 < 90, '60-89',

if(Field1 > 89 AND Field1 <120, '90-119',

if(Field1 > 119, '120+', 'N.A.')

))))

as [Description]

From ....

MK_QSL
MVP
MVP

You have some space between Field 1... make sure that Field1 and Field 1 are different in qlikview..

use below

if(Field1 < 30, 'Current',

  if(Field1 > 29 AND Field1 <60, '30-59',

  if(Field1 > 59 AND Field1 < 90, '60-89',

  if(Field1 > 89 AND Field1 <120, '90-119',

  if(Field1 > 119, '120+'))))) as [Description]

or

[Field 1] instead of Field1

Not applicable
Author

I have that exact code in my script but am still getting a syntax error. I'm going to play around with it to see if I can get it to work, but do you have any idea why this isn't working?

alexandros17
Partner - Champion III
Partner - Champion III

No Matthew, parenthesis were unbalanced, read better and look at the position of ) inside the script ...

preminqlik
Specialist II
Specialist II

Hi there try below

Load

*,

if(Field1 < 30, 'current',

if(Field1 > 29 AND Field 1 <60, '30-59',

if(Field1 > 59 AND Field1 < 90, '60-89',

if(Field1 > 89 AND Field1 <120, '90-119',

if(Field1 > 119, '120+', 'N.A.')

))))

as [Description];

SQL SELECT *

FROM SQL table name;




Greg_Williams
Employee
Employee

Another thought...have you looked into the class() function?

~Greg