Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Create a year, month, day field from a date

I have data coming from DB2 iseries via SQL statement.  How can I pull the year, month, and day from a single date field to use for selection?
Here's my script:
ODBC CONNECT TO iSeries (XUserId is xxx, XPassword is xxx);
SQL SELECT * FROM S106BB2C.REJECT.REJECTNEW where RTYRJ<>'X' and RSTATS <> 'X';
1 Solution

Accepted Solutions
oscar_ortiz
Valued Contributor

Re: Create a year, month, day field from a date

Tammy,

Try adding a preceding load statement to your SQL statement.  In this load statement you can use the vaious QlikView functions to transform your data.

So you statment might look something like this:

ODBC CONNECT TO iSeries (XUserId is xxx, XPassword is xxx);

Load

     *,

     Month(YourDateField) as myMonth,

     Day(YourDateField) as myDay,

     Year(YourDateField) as myYear

;


SQL SELECT * FROM S106BB2C.REJECT.REJECTNEW where RTYRJ<>'X' and RSTATS <> 'X';

This will work if your field is truly a date field.  I have seen date fields in DB2 stored as numeric values so you'll need to be aware of that.  If that's the case you'll want to use different functions to extract your dates.

Good Luck

Oscar

3 Replies
oscar_ortiz
Valued Contributor

Re: Create a year, month, day field from a date

Tammy,

Try adding a preceding load statement to your SQL statement.  In this load statement you can use the vaious QlikView functions to transform your data.

So you statment might look something like this:

ODBC CONNECT TO iSeries (XUserId is xxx, XPassword is xxx);

Load

     *,

     Month(YourDateField) as myMonth,

     Day(YourDateField) as myDay,

     Year(YourDateField) as myYear

;


SQL SELECT * FROM S106BB2C.REJECT.REJECTNEW where RTYRJ<>'X' and RSTATS <> 'X';

This will work if your field is truly a date field.  I have seen date fields in DB2 stored as numeric values so you'll need to be aware of that.  If that's the case you'll want to use different functions to extract your dates.

Good Luck

Oscar

Employee
Employee

Re: Create a year, month, day field from a date

Oscar's solution works excellently. However, if you have a very large transaction table, you do not want to add new fields to this table. Then you should instead use the following construction:

TransactionTable:

SQL SELECT * FROMS106BB2C.REJECT.REJECTNEW where RTYRJ<>'X' and RSTATS <> 'X';

Calendar:

Load distinct

     YourDateField,

     Month(YourDateField) as myMonth,

     Day(YourDateField) as myDay,

     Year(YourDateField) as myYear

     Resident TransactionTable;

Not applicable

Create a year, month, day field from a date

Henric,  I only have about 27,000 records so Oscars solution worked for me.  However, I believe I may use yours in the future.  I appreciate both of your quick responses!

Community Browser