Qlick cloud sense where max(date) = date

[martapardo]

I have a table with detailed of each user and he state in a course. This table is made by many concatenate tables which have an export date, so is a historic table with all the record of the years of the same people, so after that i would like a agroupation by the responsable email taking just the rows of the last export. 

So i do this and this does not work.

AGG_Historico:
LOAD
min(Responsable) as Responsable,
Responsable_email,
Responsable_email as email_DELEGADO_TERRITORIAL,
Responsable_email as email_JEFE_ZONA,
Responsable_email as email_JEFE_APARCAMIENTO,
count(Responsable_email) as total_Usuarios,
sum(NO_INICIADO) as numero_NO_INICIADO,
sum(EN_PROCESO) as numero_EN_PROCESO,
sum(COMPLETADO) as numero_COMPLETADO,
Avg(COMPLETADO) as percentage_COMPLETADO
resident Historico
where max(Fecha_exportacion) = Fecha_exportacion
group by Responsable_email;

without the where is works perfectly.

[daanciorea]

Hi,

Your problem is that max(Fecha_exportacion) is calculated for each row. You must calculate this date in a variable and then use it.

When you say max(Fecha_exportacion) = Fecha_exportacion, what do you mean?

Dan.

[martapardo]

I mean that i have the records of all year of all the users so i want just to take the records belongin the last exportation date which in spanish is Fecha_exporcion, so i would like to do the same where but max(Fecha_exportacion ) should be the maximum date in this table. 

[daanciorea]

Well, this max(Fecha_Exportacion) is distinct for all user or is equal?

[martapardo]

It will be the same since it is the last exportation date,

[daanciorea]

OK.
Try this:

Max_Fecha:
Load Date(Max(Fecha_Exportacion)) as MaxFechaExportacion Resideent Historico;
Let vMaxFechaExportacion = Peek('MaxFechaExportacion');
Drop Table Max_Fecha;

And use this variable into the where statment. Be careful with the date format.

AGG_Historico:
LOAD
min(Responsable) as Responsable,
Responsable_email,
Responsable_email as email_DELEGADO_TERRITORIAL,
Responsable_email as email_JEFE_ZONA,
Responsable_email as email_JEFE_APARCAMIENTO,
count(Responsable_email) as total_Usuarios,
sum(NO_INICIADO) as numero_NO_INICIADO,
sum(EN_PROCESO) as numero_EN_PROCESO,
sum(COMPLETADO) as numero_COMPLETADO,
Avg(COMPLETADO) as percentage_COMPLETADO
resident Historico
where Fecha_exportacion = '$(vMaxFechaExportacion)'
group by Responsable_email;

Dan.

[greend21]

If where Fecha_exportacion = '$(vMaxFechaExportacion)' doesn't work try Fecha_exportacion = vMaxFechaExportacion.  I forget how exactly single quotes work but I know without them if you have 7/5/19 it will divide 7 by 5 then divide that by 19 because it will calculate the variable.