SQL Data - Loading Date Information for Set Analysis

[Anonymous]

Hi,

After much browsing through the forum, I still can't seem to get this to work.

I have data that i am loading from an ODBC database.

ODBC CONNECT TO CVM;

SQL SELECT *

FROM CVM.dbo.ACC_QV;

SQL SELECT *

FROM CVM.dbo.CUST_QV;

Within ACC_QV there is a field:

period

There is no period in CUST_QV.

Period is a numeric field with the format '201501' for January 2015.

I need to use this period for MoM and YoY analysis.

I have tried:

ODBC CONNECT TO CVM;

SQL SELECT *

FROM CVM.dbo.CUST_QV;

Table1:

LOAD period,

Date#(period,'YYYYMM') as PeriodDate,

Month(PeriodDate) as PeriodMonth,

Year(PeriodDate) as PeriodYear;

SQL SELECT

ACCT_MK,

limit_movement,

period,

report_group,

Date#(period,'YYYYMM') as PeriodDate,

Month(PeriodDate) as PeriodMonth,

Year(PeriodDate) as PeriodYear

FROM CVM.dbo.ACC_QV;

Which keeps coming back with an Error - Date# is not a function.

Please help me in loading the Month and Year fields in the load script?

[maxgro · Accepted solution]

Which keeps coming back with an Error - Date# is not a function.

     because Date# is a Qlik function and you use that in the SQL section

     In SQL (after SQL select) section you should use SQL functions of your database

     In Qlik section (after LOAD, before SQL) you can use Qlik functions

try

LOAD

     *,

     year(PeriodDate) as PeriodYear,

     month(PeriodDate) as PeriodMonth

     ;

LOAD

     *,

     makedate(left(period,4), right(period,2)) as PeriodDate

     ;

SQL SELECT *

FROM CVM.dbo.ACC_QV;

[Anonymous]

Thank you very much for your help!