topic Re: Date conversion problem in QlikView

Date conversion problem

Wed, 25 Nov 2020 16:16:04 GMT

Dear all,

I have a problem with data conversion in an iterative process:

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep='.';
SET MoneyDecimalSep=',';
SET MoneyFormat='€ #.##0,00;€ -#.##0,00';
SET TimeFormat='h:mm:ss';
SET DateFormat='D-M-YYYY';
SET TimestampFormat='D-M-YYYY h:mm:ss[.fff]';
SET MonthNames='jan;feb;mrt;apr;mei;jun;jul;aug;sep;okt;nov;dec';
SET DayNames='ma;di;wo;do;vr;za;zo';

Let varMinDate = '1-1-2017';

Let varMaxDate = '10-1-2017';

TempCalendar:

LOAD

$(varMinDate) + Iterno()-1 As Num,

Date($(varMinDate) + IterNo() - 1) as TempDate

AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

The output is not what I have expected:

Even without single quotation mark still a problem, since I have let installed QV latest version in W10 environment.

Hope that you can explain me what went wrong.

Best regards,

Cornelis

Re: Date conversion problem

PrashantSangle — Mon, 06 Mar 2017 14:32:04 GMT

Hi,

use

date(date#('1-1-2017','DD-MM-YYYY'),'DD-MM-YYYY') as

Let varMinDate = date(date#('1-1-2017','DD-MM-YYYY'),'DD-MM-YYYY');

Let varMaxDate = date(date#('10-1-2017','DD-MM-YYYY'),'DD-MM-YYYY');

Regards,

Re: Date conversion problem

Mon, 06 Mar 2017 14:40:02 GMT

Dear Prashant,

Thank you for your speedy response, but unfortunately it does not help.

Let varMinDate = date(date#('01-01-2017','DD-MM-YYYY'),'DD-MM-YYYY');

Let varMaxDate = date(date#('10-01-2017','DD-MM-YYYY'),'DD-MM-YYYY');

the output is still the same.

I have used 1-1-2017 and 01-01-2017 but both deliver the same results.

also changed D-M-YYYY instead of DD-MM-YYYY.

Re: Date conversion problem

sunny_talwar — Mon, 06 Mar 2017 14:45:40 GMT

Try this:

Let varMinDate = Num(MakeDate(2017, 1, 1));

Let varMaxDate = Num(MakeDate(2017, 1, 10));

Re: Date conversion problem

sunny_talwar — Mon, 06 Mar 2017 14:46:41 GMT

Or even this

Let varMinDate = Num('1-1-2017');

Let varMaxDate = Num('10-1-2017');

Re: Date conversion problem

PrashantSangle — Mon, 06 Mar 2017 14:52:58 GMT

Hi,

Try below script

Let varMinDate = num(date#(num#('1-1-2017'),'DD-MM-YYYY'));

//exit Script;

Let varMaxDate = num(date#(num#('10-1-2017'),'DD-MM-YYYY'));

TempCalendar:

LOAD

$(varMinDate) + Iterno()-1 As Num,

Date($(varMinDate) + IterNo() - 1) as TempDate

AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

Regards,

Prashant

Re: Date conversion problem

Mon, 06 Mar 2017 14:53:05 GMT

Dear Sunny,

Thank you, this is the solution

I do not understand why the previous version works very well but not in a new W10 environment.

MakeDate and Num work both.

Best regards,

Cornelis

Re: Date conversion problem

sunny_talwar — Mon, 06 Mar 2017 14:56:16 GMT

Not sure what the difference is... but from what I understand is that 1-1-2017 is expanded to -2017 within the dollar sign expansion (not read as date anymore) and hence a date from before 1900 (should be 2017 days before 01/01/1900)

Re: Date conversion problem

Mon, 06 Mar 2017 15:41:05 GMT

Yes, that is something that come up into my mind.

thanks!

Re: Date conversion problem

Mon, 06 Mar 2017 15:43:52 GMT

Hi Prashant,

Thank you for your valuable contribution.

This is also the solution, Sunny has just delivered a simple solution.

Nevertheless, your contribution is an interesting point for consideration.

Best regards,

Cornelis