topic Re: Difference dates to num in QlikView

Difference dates to num

pgalvezt — Mon, 09 Jul 2012 13:55:56 GMT

Hello, Currently I have worked with Visual Fox Pro until now the model goes well, but I have a new requiriment and I couldn't figure out the idea is obtain in num from different dates. anybody knows how to obtain this I tried by script but nothing happened. The other thing is the database return me a wire date like:

00/01/1900

is there anyway to delete this date?

Thanks.

Re: Difference dates to num

swuehl — Mon, 09 Jul 2012 14:37:20 GMT

I think I haven't fully understood your problem, but in general

a) interpret your dates using date#() function or a standard date format when loading, so your dates have an internal numerical representation within QV. Then just subtract the dates to get the difference in days.

b) 00/01/1900 is surely no date.

In what format do you get your dates returned from the DB? Are you using some kind of date interpretation?

You should get false (0) returned if you try to apply isnum() to a date interpretation like

isnum(date#('00/01/1900','DD/MM/YYYY')) as IsDateFlag

Hope this helps,

Stefan

Re: Difference dates to num

pgalvezt — Mon, 09 Jul 2012 16:09:49 GMT

Hi stefan thank you for your reply. I forgot tell you that my format dates are different End Month and Expiration Oldest I need the result between End Month - Expiration Oldest but the second date appear like "-". I attached a file qvw for better comprehension.

Thank you.

Re: Difference dates to num

pgalvezt — Mon, 09 Jul 2012 17:30:08 GMT

Thank you.

Re: Difference dates to num

swuehl — Mon, 09 Jul 2012 18:40:13 GMT

Sorry, I am not going to download your 22 MB file.

But looking at your first sample, you could try something like

SET ThousandSep='.';

SET DecimalSep=',';

SET MoneyThousandSep='.';

SET MoneyDecimalSep=',';

SET MoneyFormat='#.##0,00 €;-#.##0,00 €';

SET TimeFormat='h:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY h:mm:ss[.fff]';

SET MonthNames='ene;feb;mar;abr;may;jun;jul;ago;sep;oct;nov;dic';

SET DayNames='lun;mar;mié;jue;vie;sáb;dom';

LOAD *, Date1-Date2 as Diff;

Load

Date1,

if(isnum(Date#(Date2,'DD-MMM-YY')),Date#(Date2,'DD-MMM-YY')) as Date2

Inline [Date1, Date2

01/05/2012,00/01/1900

10/05/2012,05-dic-10

20/05/2012,05-ene-11];

Re: Difference dates to num

pgalvezt — Mon, 09 Jul 2012 18:55:07 GMT

Sorry about that. The file is updated now. I was checking the data base and the data base administrator told me that end month is not a field. So I create at end month variable but I can't still get the good result.

Re: Difference dates to num

swuehl — Mon, 09 Jul 2012 20:06:13 GMT

Not sure if I understand what your problem is, but I think one issue is still not matching date formats.

Your standard date format is defined by

SET DateFormat='DD-MM-YYYY';

But the format of e.g. TD_Corte is like 'DD/MM/YYYY' (e.g. 20/05/2012), so it is not interpreted correctly as date.

You need to use

date#(td_corte,'DD/MM/YYYY') as TD_Corte,

date(date#(td_corte,'DD/MM/YYYY')) as TD_Corte,

to correctly interpret your date and then maybe format it according your standard date format.

I suggest that you check out the date# / date resp. timestamp# / timestamp functions and the date and time format codes in the Help (there is also a heap of samples in thousands of threads here in the forum related to your issue).

A good summary has been done by Henric Cronström:

http://community.qlik.com/blogs/qlikviewdesignblog/2012/06/08/get-the-dates-right

http://community.qlik.com/docs/DOC-3102

Hope this helps,

Stefan