Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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];
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
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.
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.
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];
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.
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,
or
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