Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hey all, can anyone help correct the below - I'm trying to load dimensions for a chart as I have currently calculated my chart conditions using expressions which means I can't 'drill down' in to them. When loading the below I get an error saying, 'missing syntax ','' - i can't find the missing syntax / resolve the error.
SQL SELECT
"TOTAL_TIME_ELAPSED" as [Time Elapsed],
if(([TOTAL_TIME_ELAPSED])<15, '0-15',
if(([TOTAL_TIME_ELAPSED])<30, '16-30',
if(([TOTAL_TIME_ELAPSED])<45, '31-45',
if(([TOTAL_TIME_ELAPSED])<60, '46-60',
if(([TOTAL_TIME_ELAPSED])<75, '61-75',
if(([TOTAL_TIME_ELAPSED])<90, '76-90',
if(([TOTAL_TIME_ELAPSED])>90, '90+'))))))) as Period
FROM "XXX".dbo."WIP_REPORT";
Appreciate the help.
I think you're mixing t-sql syntax and qlikview syntax. Try using a preceding load:
Load
"TOTAL_TIME_ELAPSED" as [Time Elapsed],
if(([TOTAL_TIME_ELAPSED])<=15, '0-15',
if(([TOTAL_TIME_ELAPSED])<=30, '16-30',
if(([TOTAL_TIME_ELAPSED])<=45, '31-45',
if(([TOTAL_TIME_ELAPSED])<=60, '46-60',
if(([TOTAL_TIME_ELAPSED])<=75, '61-75',
if(([TOTAL_TIME_ELAPSED])<=90, '76-90',
if(([TOTAL_TIME_ELAPSED])>90, '90+'))))))) as Period ;
SQL Select "TOTAL_TIME_ELAPSED" FROM "XXX".dbo."WIP_REPORT";
The if sentence should be in the LOAD Section, not in the SQL:
LOAD
"TOTAL_TIME_ELAPSED" as [Time Elapsed],
if(([TOTAL_TIME_ELAPSED])<15, '0-15',
if(([TOTAL_TIME_ELAPSED])<30, '16-30',
if(([TOTAL_TIME_ELAPSED])<45, '31-45',
if(([TOTAL_TIME_ELAPSED])<60, '46-60',
if(([TOTAL_TIME_ELAPSED])<75, '61-75',
if(([TOTAL_TIME_ELAPSED])<90, '76-90',
if(([TOTAL_TIME_ELAPSED])>90, '90+'))))))) as Period;
SQL
SELECT *
FROM "XXX".dbo."WIP_REPORT";
Anyway, another solution for intervals is:
Range:
LOAD * INLINE [
Min, Max, Interval, CodInterval
16, 29, <30, 1
30, 39, from 30 to 39, 2
40, 49, from 40 to 49, 3
50, 59, from 50 to 59, 4
60, 100, >59, 5
];
join IntervalMatch (field to join) LOAD Min, Max resident Range;
Left Join (Table to Join)
LOAD *
Resident Range;
Good Luck