topic Re: Problems with Date format in QlikView

Problems with Date format

ecabanas — Mon, 13 Jul 2015 11:59:48 GMT

Hi all,

I'm loading dates from sql server that comes with this format: 2011-08-25 00:00:00.000

I create a Calendar with:

LET vDateMin = Num(MakeDate(2011,1,1));

LET vDateMax = Floor(MonthEnd(Today()));

LET vDateToday = Num(Today());

//////////////////////////////////////////////////////////////////////////////////////////////////////////////////

//////////////////////////////////////////TEMPCALENDAR///////////////////////////////////////////////////////////////

//////////////////////////////////////////////////////////////////////////////////////////////////////////////////

TempCalendar:

LOAD

$(vDateMin) + RowNo() - 1 AS DateNumber,

Date($(vDateMin) + RowNo() - 1) AS TempDate

AUTOGENERATE 1

WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);

////////////////////////////////////////////////////////////////////////////////////////////////////////////////

////////////////////////////////////////CALENDAR///////////////////////////////////////////////////////////////

////////////////////////////////////////////////////////////////////////////////////////////////////////////////

Calendar:

LOAD

Floor(TempDate) AS DATEFINANCIAL_LINE,

The problem is when I load in the script:

floor(DATEFINANCIAL) as DATEFINANCIAL_LINE

There are some lines tha did not match with the Calendar and I'm getting fool to find the mistake

Many thank's

Eduard

Re: Problems with Date format

MK_QSL — Mon, 13 Jul 2015 12:05:41 GMT

Try this

LET vDateMin = Num(MakeDate(2011,1,1));

LET vDateMax = Num(MonthEnd(Today()));

LET vDateToday = Num(Today());

//////////////////////////////////////////////////////////////////////////////////////////////////////////////////

//////////////////////////////////////////TEMPCALENDAR///////////////////////////////////////////////////////////////

//////////////////////////////////////////////////////////////////////////////////////////////////////////////////

TempCalendar:

LOAD

$(vDateMin) + RowNo() - 1 AS DateNumber,

Date($(vDateMin) + RowNo() - 1) AS TempDate

AUTOGENERATE 1

WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);

////////////////////////////////////////////////////////////////////////////////////////////////////////////////

////////////////////////////////////////CALENDAR///////////////////////////////////////////////////////////////

////////////////////////////////////////////////////////////////////////////////////////////////////////////////

Calendar:

LOAD

Date(TempDate) AS DATEFINANCIAL_LINE

Resident TempCalendar;

Drop Table TempCalendar;

Date(floor(DATEFINANCIAL)) as DATEFINANCIAL_LINE

Re: Problems with Date format

sunny_talwar — Mon, 13 Jul 2015 12:07:57 GMT

May be try this:

TempCalendar:

LOAD

$(vDateMin) + RowNo() - 1 AS DateNumber,

Date($(vDateMin) + RowNo() - 1, 'YYYY-MM-DD') AS TempDate

AUTOGENERATE 1

WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);

////////////////////////////////////////////////////////////////////////////////////////////////////////////////

////////////////////////////////////////CALENDAR///////////////////////////////////////////////////////////////

////////////////////////////////////////////////////////////////////////////////////////////////////////////////

Calendar:

LOAD

TempDate AS DATEFINANCIAL_LINE,

Fact_Table:

Date(Floor(TimeStamp#(DATEFINANCIAL, 'YYYY-MM-DD')), 'YYYY-MM-DD') as DATEFINANCIAL_LINE

Re: Problems with Date format

sasiparupudi1 — Mon, 13 Jul 2015 14:11:45 GMT

LET vDateMin = Num(MakeDate(2011,1,1));

LET vDateMax = Floor(MonthEnd(Today()));

LET vDateToday = Num(Today());

TempCalendar:

LOAD

$(vDateMin) + RowNo() - 1 AS DateNumber,

Date($(vDateMin) + RowNo() - 1) AS TempDate

AUTOGENERATE 1

WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);

Cal:

LOAD *,

Floor(TempDate) AS DATEFINANCIAL_LINE Resident TempCalendar;

DROP Table TempCalendar;

Data:

Load SqlDate

,Date(if(SubStringCount(SqlDate,' ')>0,Timestamp#(SqlDate,'YYYY-MM-DD hh:mm:ss[.fff]'),SqlDate)) as SqlDate1

Inline

[

SqlDate

2011-08-25 00:00:00.000

2011-01-25

2011-02-22 00:00:00.000

]

Re: Problems with Date format

sasiparupudi1 — Tue, 14 Jul 2015 11:29:24 GMT

Is your problem solved?

Sasi

Re: Problems with Date format

ecabanas — Thu, 16 Jul 2015 10:31:48 GMT

Hi Sasidhar,

the problem was there were some dates "null" , I tryied another date field like phisical date that has allways data

Many thank's guys

Eduard

Re: Problems with Date format

sasiparupudi1 — Thu, 16 Jul 2015 10:34:47 GMT

Please close this thread then marking your resolution as a correct answer