topic Re: Today() function failing during load in QlikView

Today() function failing during load

Wed, 11 Sep 2013 08:47:38 GMT

Hi,

I finding the following problem very strange, I have the following statement in loading a table, for records which has greater INVOICE_DUE_DATE than of today, the expression is failing for some reason, even Today_date is coming as null and Invoice_Ageing_Due_Date_Days as null.

Please help.

	Today() as Today_date,
	if (INVOICE_DUE_DATE > Today(),-1, Interval(Today() - INVOICE_DUE_DATE,'D')) as Invoice_Ageing_Due_Date_Days,
	if( Today() < INVOICE_DUE_DATE ,'Not Due',
	if(Interval(Today() - INVOICE_DUE_DATE,'D') <= 30,'1-30',
	if(Interval(Today() - INVOICE_DUE_DATE,'D') <= 60, '31-60',
	if(Interval(Today() - INVOICE_DUE_DATE,'D') <= 90,'61-90','Above 90 Days'
	))))
	as Invoice_Ageing_Due_Date,

Re: Today() function failing during load

Wed, 11 Sep 2013 08:57:45 GMT

Hi,

Try wrapping your [INVOICE_DUE_DATE] in the Date() function like;

Date([INVOICE_DUE_DATE])

My guess is that the data types of today() and [INVOICE_DUE_DATE] are different.

Also, it looks as though you're trying to figure out the days difference using the interval() function. I'd advise wrapping the [INVOICE_DUE_DATE] with Floor() to ensure it shows from the beginning of the day like so;

Interval(Date(Today()) - Date(Floor(INVOICE_DUE_DATE)),'D')

Regards,

Nick

Re: Today() function failing during load

er_mohit — Wed, 11 Sep 2013 09:19:06 GMT

see the attached file

Re: Today() function failing during load

Wed, 11 Sep 2013 13:53:56 GMT

Thanks, The strange part is that function perform right when date is before the current date.

I even tried to use the expression Today() < INVOICE_DUE_DATE to avoid such case, but in that case it failed, moreover the statement

Today() as Today_date,

is a totally a separate statement but that is also returning null for cases like where date is greater than current date.

I also tried floor() and date() but no avail.

Re: Today() function failing during load

Wed, 11 Sep 2013 14:14:53 GMT

Problem solved when the data is loaded from QVD instead of Excel. Strange