topic Won't load Dates correctly from DB2 Database... in QlikView

Won't load Dates correctly from DB2 Database...

Thu, 22 Aug 2013 15:56:27 GMT

Hello,

So, I am loading data into QlikView from a DB2 database, and one field within the Database is a DATE field. However, when I load the data into QlikView, it does not recognise the date correctly.

Does anyone know how to fix this?

Thanks

Re: Won't load Dates correctly from DB2 Database...

rajeshvaswani77 — Thu, 22 Aug 2013 16:26:37 GMT

Hi,

All dates would be text when it comes from the DB2 database, you would have to use makedate function and create a date.

tahnks,

Rajesh Vaswani

Re: Won't load Dates correctly from DB2 Database...

Anonymous — Fri, 23 Aug 2013 00:14:11 GMT

Beth,

See the function date#() - it tells QlikView script how to interpret the data as date. For example:
1) if the format is YYYYMMDD, use date(date#(DateField, 'YYMMDD'))

2) if the format is MMDDYYYY, use date(date#(DateField, 'MMDDYY'))

etc.

I don't know the date format in DB2, hope the info above can help.

Regards,

Michael

Re: Won't load Dates correctly from DB2 Database...

Tue, 27 Aug 2013 07:44:56 GMT

Okay so I have tried everything that you have suggested with no luck. I have even manipulated them to try and achieve my aim and yet QlikView still won't recognise my date field.

Any help?

Re: Won't load Dates correctly from DB2 Database...

rajeshvaswani77 — Tue, 27 Aug 2013 07:49:39 GMT

Well, if the dates from DB2 are all strings then you need to treat them as string rather than date. Then you will end up stripping the date string to get the date month and year using the string functions like left, mid, right. Once you have done that you could use makedate where you will not pass the year month and date to get the date in QlikView. I am assuming that you have got all the data from DB2 in text extracts.

Hope this helps.

thanks,

Rajesh Vaswani

Re: Won't load Dates correctly from DB2 Database...

jagan — Tue, 27 Aug 2013 07:55:19 GMT

Hi,

Can you attach some sample dates, so that it would be helpful to give the solution.

Regards,

Jagan.

Re: Won't load Dates correctly from DB2 Database...

Tue, 27 Aug 2013 08:00:14 GMT

Within my DB2 database I have a column called Process Date, where the Data Type is Date.

When I ask QlikView to load these, I write:

LOAD PROCESS_DATE as "PROCESS_DATE";

SQL SELECT PROCESS_DATE FROM TABLE;

Now, I have the following dates entered in my database:

02/06/2013

09/08/2013

18/08/2013

and before you say it's because it is not in the US form, the Database knows what is the day and what is the month. I have checked.

However, QlikView sees these days as the following:

06/00/2013

08/00/2013

And it sees the dates like this or not at all when I use the previous methods mentioned.

Re: Won't load Dates correctly from DB2 Database...

rajeshvaswani77 — Tue, 27 Aug 2013 08:12:34 GMT

Hi Could you please try to change the SQL statement that will bring the date to QlikView.

TO_DATE(PROCESS_DATE,'DD/MM/YYYY')

thanks,

Rajesh Vaswani

Re: Won't load Dates correctly from DB2 Database...

jagan — Tue, 27 Aug 2013 08:22:27 GMT

Hi,

Try this script

LOAD
Date(DAte#(PROCESS_DATE, 'DD/MM/YYYY'), 'MM/DD/YYYY') as "PROCESS_DATE";

SQL SELECT PROCESS_DATE FROM TABLE;

Regards,

Jagan.

Re: Won't load Dates correctly from DB2 Database...

Tue, 27 Aug 2013 08:46:04 GMT

Hi have tried that, but when I select the drop down (it is in a multi-box object) nothing happens, no data is shown?

Re: Won't load Dates correctly from DB2 Database...

rbecher — Tue, 27 Aug 2013 08:52:49 GMT

Hi Beth,

what is your default QlikView date format?

SET DateFormat=...?

- Ralf

Re: Won't load Dates correctly from DB2 Database...

Tue, 27 Aug 2013 08:57:39 GMT

Hi, it is set to the Default 'DD/MM/YYYY'

Re: Won't load Dates correctly from DB2 Database...

rbecher — Tue, 27 Aug 2013 09:00:38 GMT

What do you get with:

LOAD
Text(PROCESS_DATE) as "PROCESS_DATE";

SQL SELECT PROCESS_DATE FROM TABLE;

Re: Won't load Dates correctly from DB2 Database...

Tue, 27 Aug 2013 09:02:34 GMT

I get:

41311

41313

41525

Re: Won't load Dates correctly from DB2 Database...

jagan — Tue, 27 Aug 2013 09:10:36 GMT

HI,

Try this

LOAD
Date(Num(Text(PROCESS_DATE))) as "PROCESS_DATE";

SQL SELECT PROCESS_DATE FROM TABLE;

Regards,

Jagan.

Re: Won't load Dates correctly from DB2 Database...

Tue, 27 Aug 2013 09:12:37 GMT

Once again nothing appears in the drop down

Re: Won't load Dates correctly from DB2 Database...

jagan — Tue, 27 Aug 2013 09:14:48 GMT

Hi,

Can you attach the sample file.

Regards,

jagan.

Re: Won't load Dates correctly from DB2 Database...

Wed, 28 Aug 2013 12:09:57 GMT

Have solved this. It seems QlikView can't read date fields but can read TimeStamp fields.