Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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]
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]
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?
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 ....
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
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?
No Matthew, parenthesis were unbalanced, read better and look at the position of ) inside the script ...
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;
Another thought...have you looked into the class() function?
~Greg