Hi, i need to capture de last Situation of the users attending to a top Date (p.e. <01/10/2010 = DD/MM/YYYY) and >=01/09/2010, but maybe the last situation of the user where the 27/07/2010, then it too have to appear, because it not had any other modification of situation at least until <01/10/2010.
The script i have is this:
Historico: LOAD fecha as FechaHistorico, year(fecha) & '/' & timestamp(fecha,'MM') as MesAñoHistorico, usuario as codUsuario, ApplyMap('Mapa_Situacion',situacion) as SituacionHistorico, ApplyMap('Mapa_Motivo',situacion & motivo) as MotivoHistorico, SQL SELECT fecha, usuario, situacion, motivo FROM historic; INNER JOIN (Historico) LOAD usuario as codUsuario, max(fecha) as UltHistorico, lastvalue(ApplyMap('Mapa_Situacion',situacion)) as UltSituHistorico, lastvalue(ApplyMap('Mapa_Motivo',situacion & motivo)) as UltiMotiHistorico where date(fecha,'DD/MM/YYYY') >= date($(InicioFechaTope),'DD/MM/YYYY') and date(fecha,'DD/MM/YYYY') < date($(FechaTope),'DD/MM/YYYY') group by usuario; sql select fecha, situacion, motivo, usuario from historic;
As I am using the variable $(InicioFechaTope), i know it's impossible to get the situation of 27/07/2010, but if don't how to filter the datas to how were into a particular month.
So if i have:
ID CodUsuario FechaHistorico SituacionHistorico MotivoHistorico 1 A 05/08/2010 PENDIENTE ALTA FAMILIAR 2 B 06/08/2010 ACTIVO ACTIVO 3 A 27/08/2010 ACTIVO ACTIVO 4 C 27/09/2010 PENDIENTE ALTA PETICION PERSONAL 5 B 30/09/2010 SUSPENSION VACACIONES 6 C 02/10/2010 ACTIVO ACTIVO
then when select SituacionHistorico = ACTIVO i need to get: ID CodUsuario FechaHistorico SituacionHistorico MotivoHistorico 3 A 27/08/2010 ACTIVO ACTIVO // because doesn't have any other situation
the user B not because he has a new situation before 01/10/2010 the user C not becasue the before 01/10/2010 his last situation wasn't ACTIVO
