topic Re: Create a year, month, day field from a date in QlikView

Create a year, month, day field from a date

Thu, 24 May 2012 20:18:37 GMT

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

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

oscar_ortiz — Thu, 24 May 2012 21:04:16 GMT

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

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

hic — Fri, 25 May 2012 06:59:20 GMT

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;

Create a year, month, day field from a date

Fri, 25 May 2012 14:01:44 GMT

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!