Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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

Re: Inserting field with an if statement

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]

Re: Inserting field with an if statement

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

Re: Inserting field with an if statement

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?

Re: Inserting field with an if statement

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

Re: Inserting field with an if statement

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

Re: Inserting field with an if statement

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?

Re: Inserting field with an if statement

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

preminqlik
Valued Contributor II

Re: Inserting field with an if statement

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;




Employee
Employee

Re: Inserting field with an if statement

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

~Greg

Community Browser