Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
paulyeo11
Valued Contributor II

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

Re: Recode YearMonth & YearQtr return error msg

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
Valued Contributor II

Re: Recode YearMonth & YearQtr return error msg

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

MVP
MVP

Re: Recode YearMonth & YearQtr return error msg

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

Re: Recode YearMonth & YearQtr return error msg

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;

Community Browser