topic Date format problem in QlikView

Date format problem

qw_johan — Sat, 12 Nov 2011 10:46:20 GMT

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,

Date format problem

swuehl — Sat, 12 Nov 2011 11:34:50 GMT

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

Date format problem

qw_johan — Sat, 12 Nov 2011 13:59:58 GMT

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