Datetime SQL problem

[#1]

Why is this date is not within my selection?

Thanks

[Anonymous]

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.

[jonathandienst]

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

[#4]

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";

[#5]

No Jonathan there is linked....

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

[Anonymous]

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?

[#7]

Doesn't work

Paco

[jonathandienst · Accepted solution]

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

[Anonymous]

To lose the time stamp you can use :

Floor()

That should work, I hope.

[Anonymous]

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

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