Recode YearMonth & YearQtr return error msg

[paulyeo11]

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

[maxgro]

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

          ....

          ;

[paulyeo11]

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

[maxgro]

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

[#5]

I think This Statements can help you out. Try Once

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

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