topic Datetime SQL problem in QlikView

Datetime SQL problem

Mon, 24 Jan 2011 18:48:20 GMT

Why is this date is not within my selection?

Thanks

Datetime SQL problem

Anonymous — Mon, 24 Jan 2011 18:58:50 GMT

Sorry, I need a bit more information to say something useful about this.

Can you maybe upload the Qlikview Document, then we can have a look.

Datetime SQL problem

jonathandienst — Mon, 24 Jan 2011 19:03:29 GMT

Paco

If I have understood your question, I suspect that the Ano and Mes fields are not linked in any way to the Posting Date. Therefore, a selection of Posting Date does not reflect in the two selectors.

Jonathan

Datetime SQL problem

Mon, 24 Jan 2011 19:03:48 GMT

Variables and Calendar

SET ThousandSep='.';
SET DecimalSep=',';
SET MoneyThousandSep='.';
SET MoneyDecimalSep=',';
SET MoneyFormat='#.##0,00 €;-#.##0,00 €';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY' hh:mm:ss[.fff];
SET MonthNames='Enero;Febrero;Marzo;Abril;Mayo;Junio;Julio;Agosto;Septiembre;Octubre;Noviembre;Diciembre';
SET DayNames='Lunes;Martes;Miercoles;Jueves;Viernes;Sábado;Domingo';

LET varMinDate = '38353';
LET vToday = num(today());

Section Application;

//*************** Temporary Calendar ***************
TempCalendar:
LOAD
$(varMinDate) + rowno() - 1 AS Num,
date($(varMinDate) + rowno() - 1) AS TempDate
AUTOGENERATE
vToday - $(varMinDate) + 1;

MasterCalendar:

LOAD
TempDate AS "Posting Date",
Week(TempDate) AS Semana,
Year(TempDate) AS Año,
Month(TempDate) AS Mes,
Day(TempDate) AS Día,
Weekday(TempDate) AS DíaSemana,
'Trim.' & ceil(month(TempDate )/3) AS Trimestre,
'Sem.' & ceil(month(TempDate )/6) AS Semestre,
Date(monthstart(TempDate), 'MMM-YYYY') AS
MesAño,
Week(TempDate)&'-'&Year(TempDate) AS SemanaAño,
inyeartodate(TempDate, $(vToday), 0) * -1 AS
CurYTDFlag,
inyeartodate(TempDate, $(vToday), -1) * -1 AS
LastYTDFlag
RESIDENT TempCalendar
ORDER BY TempDate ASC;
DROP TABLE TempCalendar;

SQL select

tempBL:
LOAD
ApplyMap('MAP_FilaBL',ApplyMap('MAP_GL_AccoumtBL',"G_L Account No_"),null( )) as FilaBL,
ApplyMap('MAP_ConceptoBL',ApplyMap('MAP_GL_AccoumtBL',"G_L Account No_"),null( )) as ConceptoBL,
ApplyMap('MAP_GL_AccoumtBL',"G_L Account No_",null( )) as SumatorioBL,
"Entry No_",
"G_L Account No_",
Date("Posting Date" ,'YYYY/MM/DD')as "Posting Date",
Amount;
SQL SELECT
"Entry No_",
"G_L Account No_",
"Posting Date",
Amount
FROM fusion2009.dbo."Engel - Axil (ES)$G_L Entry";

Datetime SQL problem

Mon, 24 Jan 2011 19:06:14 GMT

No Jonathan there is linked....

The problen is the time (dd/mm/yyyy 23:59:59) all of this datetime excludes.

Datetime SQL problem

Anonymous — Mon, 24 Jan 2011 19:15:48 GMT

You are right it must have something to do with the format

Did you try to make the format in both tables the same?

Also use:

Date("Posting Date" ,'YYYY/MM/DD')as "Posting Date",

In your master calendar.

Maybe?

Datetime SQL problem

Mon, 24 Jan 2011 19:21:36 GMT

Doesn't work

Paco

Datetime SQL problem

jonathandienst — Mon, 24 Jan 2011 19:38:02 GMT

Paco

I think what is happening is that the Posting dates from the SQL load are datetime values with a time component. 31/12/2007 23:59:59 does not relate to one of the (whole number) dates from the master calendar (and therefore is not related to an Ano and Mes value).

I think you may have expected that the date format would remove the time component. This is a common misconception. The date format changes the display representation of the date, but the internal value is not affected. To achieve that you should use the floor() function in your SQL load:

Date(Floor("Posting Date") ,'YYYY/MM/DD')as "Posting Date",

(As an aside - you have a different format in your master calendar for the posting date. I don't think that this is causing your problem, but its a little inconstant and may be confusing to users).

Hope that helps

Jonathan

Datetime SQL problem

Anonymous — Mon, 24 Jan 2011 19:38:03 GMT

To lose the time stamp you can use :

Floor()

That should work, I hope.

Datetime SQL problem

Anonymous — Mon, 24 Jan 2011 19:42:33 GMT

If it works, credit should go to Miguel Angel Baeyens . He helpt me with this one.

http://community.qlik.com/forums/t/34210.aspx

Datetime SQL problem

Tue, 25 Jan 2011 09:05:04 GMT

Thanks Honathan and Dennis...

You know how to add 1 second to a timestamp???

Paco

Datetime SQL problem

Tue, 25 Jan 2011 09:40:53 GMT

Forgetting the question.

I've taken a look at this link, and I found the solution.

http://community.qlik.com/forums/t/34210.aspx

Round(Num(Date("Posting Date", 'DD/MM/YYYY'))) AS "Posting Date"

Thanks again Dennis