4 Replies Latest reply: Jul 29, 2015 2:13 PM by siva krishna RSS

    Recode YearMonth & YearQtr return error msg

    PAUL YEO

      Hi All

       

      I have the below 2 line of script , when i run while i read data from SQL , i will get error :-

      //date(Month(date_time),'YY MMM') as YearMonth,

      //Right(year(date_time),2) & '-Q' & ceil(Month(date_time)/3) as YearQtr,

       

      May i know how to edit the above script , so that no error will occur ?

       

      Enclosed my QVW file .

       

      Paul

        • Re: Recode YearMonth & YearQtr return error msg
          Massimo Grossi

          after SQL you must use the syntax of your database (I think sql server, in bold)

          before SQL (after load) the QlikView syntax

          you cannot use Qlik function in SQL section

           

           

          load

               ....

               // here Qlik syntax

               .....

               ;

          SQL

               select

                    ...

                    // here sql server syntax

                    ....

                    ;

            • Re: Recode YearMonth & YearQtr return error msg
              PAUL YEO

              Hi Mass

               

              Thank you for your reply , i am not quite clear what you try to said . can you share with me again why ?

               

              year(date_time) as year,

              month(date_time) as month

               

              Above 2 line of script with out give me error ? which the above 2 line of script after SQL script.

               

              Paul

                • Re: Recode YearMonth & YearQtr return error msg
                  Massimo Grossi

                  I think these lines are ok in sql server (I assumed is your db)

                   

                  year(dat_data),

                  month(dat_data)

                   

                   

                  but these lines aren't ok  (and you asked for these lines in your first post) in the SQL section

                   

                  date(Month(date_time),'YY MMM') as YearMonth,

                  Right(year(date_time),2) & '-Q' & ceil(Month(date_time)/3) as YearQtr,

                   

                   

                   

                  if you want yearmonth and yearqtr try this

                   

                  LOAD

                       *,

                       date(monthstart(date_time),'YY MMM') as YearMonth,

                       Right(year(date_time),2) & '-Q' & ceil(Month(date_time)/3) as YearQtr;

                  SQL SELECT

                  line_id as line_id,

                  product_id as product_id,

                  kode_reject as kode_reject,

                  operator_id as operator_id,

                  shift_id as shift_id,

                  dies_id as dies_id,

                  date_time as date_time,

                   

                   

                  year(date_time) as year,

                  //date(Month(date_time),'YY MMM') as YearMonth,

                  //Right(year(date_time),2) & '-Q' & ceil(Month(date_time)/3) as YearQtr,

                  //num(month(date_time)) as month

                  month(date_time) as month

                  //Date(Monthstart([date_time]), 'YY MMM') as YearMonth

                   

                   

                   

                   

                  FROM andonDatabas.dbo."record_reject_detail_plc";