Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us for a live Q&A! September 21, 10 AM ET - Onboarding Fast in Qlik Sense SaaS! REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
fyrworx25
Contributor III
Contributor III

SQL Data - Loading Date Information for Set Analysis

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?

1 Solution

Accepted Solutions
maxgro
MVP
MVP

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;

View solution in original post

2 Replies
maxgro
MVP
MVP

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;

View solution in original post

fyrworx25
Contributor III
Contributor III
Author

Thank you very much for your help!