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.
qw_johan
Contributor

Date format problem

Hi!

I am having problems formatting a date while loading. No errors, but Qlikview doesn't format it correctly.

I am reading dates from an Excel spreadsheet. All dates in that Excel document are in YYYY-MM-DD format.

When I am loading my qv document I want to change all dates after October 31 2011 to October 31 2011.

In my script it looks like this:

LOAD.....

Date(if(date_to>'2011-10-31',date_to='2011-10-31',date_to)) as date_to

...

Qlikview changes all matching dates to 1899-12-30.

Why...what am I doing wrong?

Thanks,

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Date format problem

Your THEN statement is another condition, not an assignement, in this case, so it will return 0, which means 1899-12-30.

Just use the literal instead, the assignment is taking place within the load, i.e.

...

Date(if(date_to>'2011-10-31', '2011-10-31', date_to)) as date_to

...

Hope this helps,

Stefan

2 Replies
MVP
MVP

Date format problem

Your THEN statement is another condition, not an assignement, in this case, so it will return 0, which means 1899-12-30.

Just use the literal instead, the assignment is taking place within the load, i.e.

...

Date(if(date_to>'2011-10-31', '2011-10-31', date_to)) as date_to

...

Hope this helps,

Stefan

qw_johan
Contributor

Date format problem

Thanks Stefan. Very helpful.

I knew I was doing something wrong. I didn't know that QlikView would interpret that as another condition when all I wanted to do was to assign a value to the variable date_to. But now I know.

Thanks again,

Johan

Community Browser