topic Re: SQL Data - Loading Date Information for Set Analysis in QlikView

SQL Data - Loading Date Information for Set Analysis

Anonymous — Wed, 29 Jul 2015 10:38:54 GMT

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?

Re: SQL Data - Loading Date Information for Set Analysis

maxgro — Wed, 29 Jul 2015 23:10:58 GMT

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;

Re: SQL Data - Loading Date Information for Set Analysis

Anonymous — Thu, 30 Jul 2015 13:07:33 GMT

Thank you very much for your help!