Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
paulyeo11
Master
Master

Recode YearMonth & YearQtr return error msg

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

4 Replies
maxgro
MVP
MVP

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
Master
Master
Author

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
MVP
MVP

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

Not applicable

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;