topic Re: Recode YearMonth & YearQtr return error msg in QlikView

Recode YearMonth & YearQtr return error msg

paulyeo11 — Wed, 29 Jul 2015 15:47:40 GMT

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

maxgro — Wed, 29 Jul 2015 16:00:08 GMT

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

paulyeo11 — Wed, 29 Jul 2015 16:08:46 GMT

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

maxgro — Wed, 29 Jul 2015 16:24:02 GMT

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

Re: Recode YearMonth & YearQtr return error msg

Wed, 29 Jul 2015 18:13:30 GMT

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;