Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Employee
Employee

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

View solution in original post

12 Replies
somenathroy
Creator III
Creator III

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
Author

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

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
Author

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

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
Author

Hey,

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

mov
Employee
Employee

sure 🙂

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

LOAD
     date_added
;
SELECT
date_added
from mysql_database.orders;

Not applicable
Author

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

mov
Employee
Employee

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