2 Replies Latest reply: Aug 13, 2013 10:44 AM by IOSU GARCIA RSS

    Chart Dimensions - Loading in Script vs. as Expression in Chart

      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.

        • Re: Chart Dimensions - Loading in Script vs. as Expression in Chart
          Gysbert Wassenaar

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

          • Re: Chart Dimensions - Loading in Script vs. as Expression in Chart

            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