topic Re: Date Format Changes in QlikView

Date Format Changes

gireesh1216 — Wed, 25 Nov 2020 16:16:04 GMT

How to change number format to date format?

Example Number:-693593

I am using DATE(-693593 ) function.getting 01-01-0001.

how to convert -693593 to 01-01-2001 ????

Re: Date Format Changes

Miguel_Angel_Baeyens — Fri, 21 Oct 2016 08:20:20 GMT

Hi Gireesh,

Indeed, dates cannot be negative numbers in QlikView[1]. First you will need to Fabs(Field) to remove the negative number without affecting the value and then use the Date() function.

Miguel

[1] For the sake of being as technically accurate as possible, dates can be whatever number with whatever sign, integer or with decimals, positive or negative. Noting that Day 1 for the Date() function in QlikView is 31/12/1899 (DD/MM/YYYY), any day with negative sign means it happens before that day. If that is the date coming from the source, it is likely that the field in the source is empty or the day 1 for that source system, which could be whatever value.

Re: Date Format Changes

gireesh1216 — Fri, 21 Oct 2016 08:52:13 GMT

Thanks Miguel...

But How to remove negative numbers in QlikView? please help me on this...

Re: Date Format Changes

Miguel_Angel_Baeyens — Fri, 21 Oct 2016 09:01:42 GMT

I already told you, use the Fabs() function on the date in the script:

Date(Fabs(DateField)) AS Date

Miguel

Re: Date Format Changes

gireesh1216 — Fri, 21 Oct 2016 09:15:55 GMT

Hi Miguel....

I am using Date(fabs(-693593)) as Date_join.But iam getting wrong date(12/27/3798)..

Re: Date Format Changes

Miguel_Angel_Baeyens — Fri, 21 Oct 2016 09:23:50 GMT

Yes, 693593 is that future date for QlikView.

In QlikView, Day 1 is 31/12/1899, and today is day 42644, so I don't know where that date is coming from but that seems definitely wrong.

Re: Date Format Changes

Anil_Babu_Samineni — Fri, 21 Oct 2016 09:58:14 GMT

Miguel, I think to day is day 42663

And Gireesh, You might have few Forecast Data for future

LOAD *, Date(fabs(Date),'DD-MM-YYYY') as Date1;

LOAD * Inline [

Date

-693593

-693543

-693234

];

Screen Shot which i got is below for Date1

Re: Date Format Changes

gireesh1216 — Fri, 21 Oct 2016 10:01:32 GMT

hi,

use DATE(-693593 ) function u will get 01-01-0001.

I want 0001 to 2001

Re: Date Format Changes

vcanale — Fri, 21 Oct 2016 10:16:22 GMT

Hi,
from Miguel reply Date(fabs('NegativeNumb')) works correctly, but where "-693593" comes from?

01-01-2001 --> 36892
eg: Date(Fabs('-36892')) returns 01-01-2001

Re: Date Format Changes

gireesh1216 — Fri, 21 Oct 2016 10:18:03 GMT

In database showing data for one employee

After I loaded in qlikview getting below data:

USING DATE(FIELD NAME ) GETTING BELOW DATA:

finally i want date format in 01/01/2001

Date Format Changes

Re: Date Format Changes

tresB — Fri, 21 Oct 2016 10:20:56 GMT

Do you have a field with such many negative values? If yes, please post few more such values and expected output dates against them, so that we could figure out a formula.

Re: Date Format Changes

gireesh1216 — Fri, 21 Oct 2016 10:22:03 GMT

Hi please see last replay...

Re: Date Format Changes

gireesh1216 — Fri, 21 Oct 2016 10:24:01 GMT

No...I am getting only one negative value that is (-693593) only...

Re: Date Format Changes

tresB — Fri, 21 Oct 2016 10:29:26 GMT

Then you may try a static fix like:

Date(If( [Termination Date]<0, MakeDate(2001,1,1), [Termination Date])) as [Termination Date]

Re: Date Format Changes

gireesh1216 — Fri, 21 Oct 2016 10:31:03 GMT

This expression is true?

=date(if(Termination_date='-693593','36892',Termination_date))

please help me

Re: Date Format Changes

vcanale — Fri, 21 Oct 2016 10:32:39 GMT

Hi,
if you test it in a text object
=date('01-01-01') returns 01/01/2001
=date('01-JAN-01') returns '-'
with an INLINE tbl or something else try to find a way to convert JAN to 01
check also the SET DateFormat in your script

Re: Date Format Changes

Anil_Babu_Samineni — Fri, 21 Oct 2016 10:34:03 GMT

Yes, this is true this will give 01-01-2001 only. But, tresco solution is bit make sense

Try over there

Re: Date Format Changes

tresB — Fri, 21 Oct 2016 10:35:55 GMT

I would prefer a numeric comparison (removing quotes) like:

date(if(Termination_date=-693593 ,36892,Termination_date))

Re: Date Format Changes

rwunderlich — Fri, 21 Oct 2016 10:49:46 GMT

‌MIguel,

I'm not sure what this particular date number represents, but date numbers can be negative for dates prior to Dec 31, 1899.

Rob

Re: Date Format Changes

Miguel_Angel_Baeyens — Fri, 21 Oct 2016 11:07:38 GMT

Rob,

I should have been more specific, while technically they can be whatever length and sign, I don't think you ever analyze anything prior to 1899, so if you are getting negative numbers for dates that implies either a zero date (whatever is in the source system) or bad formatting.

I'll correct my prior post with this regard.

Miguel

EDIT: Actually, 01/01/0001 is a valid date. However, I hesitate you have anything which happened on 01/01/0001.