topic Re: Inserting field with an if statement in QlikView

Inserting field with an if statement

Mon, 14 Apr 2014 15:22:51 GMT

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

Re: Inserting field with an if statement

MK_QSL — Mon, 14 Apr 2014 15:24:48 GMT

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

alexandros17 — Mon, 14 Apr 2014 15:25:09 GMT

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]

Re: Inserting field with an if statement

Mon, 14 Apr 2014 15:34:32 GMT

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

alexandros17 — Mon, 14 Apr 2014 15:39:02 GMT

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

MK_QSL — Mon, 14 Apr 2014 15:44:29 GMT

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]

[Field 1] instead of Field1

Re: Inserting field with an if statement

Mon, 14 Apr 2014 15:46:24 GMT

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

alexandros17 — Mon, 14 Apr 2014 16:02:14 GMT

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

Re: Inserting field with an if statement

preminqlik — Mon, 14 Apr 2014 17:33:33 GMT

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;

Re: Inserting field with an if statement

Greg_Williams — Mon, 14 Apr 2014 17:42:49 GMT

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

~Greg

Re: Inserting field with an if statement

Mon, 14 Apr 2014 17:52:19 GMT

Hi Mathew,

you can also use like this

TableName:

SQL SELECT

FROM SQL table name;

Final:

load

"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]

Resident TableName;

drop table TableName;

Hope this help 🙂

Regards

Deepak

Re: Inserting field with an if statement

maxgro — Mon, 14 Apr 2014 18:28:32 GMT

I think after SQL SELECT

you can use the syntax of the database, not the syntax of qlikview

does your database support if .... ?

if you want to use Qlikview syntax use

Table:

// start Qlik

LOAD

if................ as ........

;

// end Qlik

// start db

SQL

SELECT

SQL SELECT

"Field1",

"Field2"

FROM SQL table name;