topic Re: Problems with Dateformat and matching date formats in QlikView

Problems with Dateformat and matching date formats

Sun, 24 Mar 2013 23:50:52 GMT

Hey everyone,

i have a problem with matching my data from two different sources:

1. mysql Database with the date format: "30.11.2011 17:23:42" -> "DD.MM.YYYY hh:mm:ss"

2. Google Adwords with the date format: "20111130" -> "YYYYMMDD"

My goal is to have a matching "date" field and the possibilty to select Year, Month and week.

1. Mysql: when i add

year(date_added) as year,

month(date_added) as month,

week(date_added) as week

to my query i get in return:

Year -> proper years like 2011,2012 etc.

Month -> displayed as 1,2,3...etc but not as march, may, june etc.

Week -> displayed as 1,2,3

I still nee a query which kills the timestamp for me so i only get the date DD.MM.YYYY

2. Google Adwords: i added the code

dim_date as dim_date,

makedate(mid(dim_date, 1, 4), mid(dim_date, 5, 2), mid(dim_date, 7, 2)) as date,

this returns the date in the wished format: DD.MM.YYYY

But then wenn i try to get the pairing with the other data i get funny stuff coming out

month(dim_date) as month,

week(dim_date)as week,

year(dim_date) as year

Year -> strange number such as 57014

Month ->in contrary to the mysql query it returns in May, june, july...

Week -> displayed as 1,2,3

When i select the date from the adwords data like 01.01.2013 it gives me as corresponding month may and some random year. See screenshot attached.

Re: Problems with Dateformat and matching date formats

somenathroy — Mon, 25 Mar 2013 04:13:29 GMT

Hi,

You have to format Extracted Date from sources with Date() and Date#() functions.

1. For MySql:

Date(Date#(Mid(date_added,1,10),'DD.MM.YYYY'),'DD.MM.YYYY') as date,

Year(Date(Date#(Mid(date_added,1,10),'DD.MM.YYYY'),'DD.MM.YYYY')) as year,

Month(Date(Date#(Mid(date_added,1,10),'DD.MM.YYYY'),'DD.MM.YYYY')) as month,

Week(Date(Date#(Mid(date_added,1,10),'DD.MM.YYYY'),'DD.MM.YYYY')) as week,

2. For Google Adwords:

Date(Date#(dim_date,'YYYYMMDD'),'DD.MM.YYYY') as date,

Year(Date(Date#(dim_date,'YYYYMMDD'),'DD.MM.YYYY')) as year,

Month(Date(Date#(dim_date,'YYYYMMDD'),'DD.MM.YYYY') ) as month,

Week(Date(Date#(dim_date,'YYYYMMDD'),'DD.MM.YYYY')) as week

Regards,

som

Re: Problems with Dateformat and matching date formats

Mon, 25 Mar 2013 15:41:11 GMT

Hey,

the adwords part worked like a charm. With the other data i am having problems:

this is the query i am using:

------

SELECT

date_added as date_added,

Date(Date#(Mid(date_added,1,10),'DD.MM.YYYY'),'DD.MM.YYYY') as date,

Year(Date(Date#(Mid(date_added,1,10),'DD.MM.YYYY'),'DD.MM.YYYY')) as year,

Month(Date(Date#(Mid(date_added,1,10),'DD.MM.YYYY'),'DD.MM.YYYY')) as month,

Week(Date(Date#(Mid(date_added,1,10),'DD.MM.YYYY'),'DD.MM.YYYY')) as week

from mysql_database.orders

----

i get this Script error:

SQL##f - SqlState: 37000, ErrorCode: 1064, ErrorMsg: [MySQL][ODBC 5.2(a) Driver][mysqld-5.1.66-0+squeeze1-log]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Year(Date(Date#(Mid(date_added,1,10),'DD.MM.YYYY'),'DD.MM.YYYY')) as year,

Mont' at line 5

Can you help?

Re: Problems with Dateformat and matching date formats

Anonymous — Mon, 25 Mar 2013 15:48:11 GMT

Chris,

You cannot use QlikView functions in SQL, it doesn't understand this. Use preceeding load, like this:

LOAD
date_added,

     Date(Date#(Mid(date_added,1,10),'DD.MM.YYYY'),'DD.MM.YYYY') as date,
     Year(Date(Date#(Mid(date_added,1,10),'DD.MM.YYYY'),'DD.MM.YYYY')) as year,
     Month(Date(Date#(Mid(date_added,1,10),'DD.MM.YYYY'),'DD.MM.YYYY')) as month,
     Week(Date(Date#(Mid(date_added,1,10),'DD.MM.YYYY'),'DD.MM.YYYY')) as week
;
SELECT
date_added
from mysql_database.orders;

Regards,
Michael

Re: Problems with Dateformat and matching date formats

Mon, 25 Mar 2013 16:05:57 GMT

Hey, thanks alot. Maybe i have fault in my thinking. The database format in the mysql table is:

2012-05-29 11:58:55

@Michael: with your help i dont get the error but i also get no values.

Re: Problems with Dateformat and matching date formats

Anonymous — Mon, 25 Mar 2013 16:27:20 GMT

If the data format is different, you have to replace this

Date#(Mid(date_added,1,10),'DD.MM.YYYY')

with

Date#(Mid(date_added,1,10),'YYYY-MM-DD')

Re: Problems with Dateformat and matching date formats

Mon, 25 Mar 2013 16:41:19 GMT

Hey,

tried that. Still doenst return any right data. Any other ideas?

Re: Problems with Dateformat and matching date formats

Anonymous — Mon, 25 Mar 2013 16:44:53 GMT

sure 🙂

Try this, and tell what it returns, if anything:

LOAD
date_added
;
SELECT
date_added
from mysql_database.orders;

Re: Problems with Dateformat and matching date formats

Mon, 25 Mar 2013 16:57:58 GMT

Hey, got it working. I put your the normal format.

week(date_added) as week,

etc..

before the

date_added

from mysql_live:orders;

But now i have one last problem:

Google adwords gives me back Month as March,June etc.

Mysql gives me back 1,2,3 etc.

thats my last question. Thanks so far for helping out!!!

Re: Problems with Dateformat and matching date formats

Anonymous — Mon, 25 Mar 2013 17:35:53 GMT

That means that the date_added in mySQL is in fact a date field, no need for date#().

Month - can't you use simply month(date_added)?

Re: Problems with Dateformat and matching date formats

Mon, 25 Mar 2013 17:46:42 GMT

i did that. with the adwords data it returns values:

Jan, Feb, March, etc.

with the mysql values it gives me

1,2,3,4 etc..

Re: Problems with Dateformat and matching date formats

Anonymous — Mon, 25 Mar 2013 17:52:46 GMT

Not clear what you're doing and what format you want. This will return Jan, Feb:

LOAD

month(date_added) as Month

SELECT date_added FROM from mysql_database.orders;

Not sure where1,2,3... are coming from. Anyway, if you have a Month field in this format, and want Jan, Feb, Mar... you can convert:

month(makedate(2013, Month)) as Month

(2013 is an example, you can use any year >=0)

Re: Problems with Dateformat and matching date formats

Sun, 31 Mar 2013 15:31:17 GMT

Thanks alot for helping out. I appreciate it.