Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Problems with Dateformat and matching date formats

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.

1 Solution

Accepted Solutions
mov
Esteemed Contributor III

Re: Problems with Dateformat and matching date formats

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

12 Replies
somenathroy
Contributor III

Re: Problems with Dateformat and matching date formats

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

Not applicable

Re: Problems with Dateformat and matching date formats

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?

mov
Esteemed Contributor III

Re: Problems with Dateformat and matching date formats

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

Not applicable

Re: Problems with Dateformat and matching date formats

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.

mov
Esteemed Contributor III

Re: Problems with Dateformat and matching date formats

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

Not applicable

Re: Problems with Dateformat and matching date formats

Hey,

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

mov
Esteemed Contributor III

Re: Problems with Dateformat and matching date formats

sure :-)

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

LOAD
     date_added
;
SELECT
date_added
from mysql_database.orders;

Not applicable

Re: Problems with Dateformat and matching date formats

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

week(date_added) as week,

etc..

before the

date_added

from mysql_liveSmiley Surprisedrders;

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!!!

mov
Esteemed Contributor III

Re: Problems with Dateformat and matching date formats

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)?

Community Browser