Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
martapardo
Contributor
Contributor

Qlick cloud sense where max(date) = date

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.

 

 

6 Replies
daanciorea
Partner - Contributor III
Partner - Contributor III

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
Contributor
Contributor
Author

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
Partner - Contributor III
Partner - Contributor III

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


martapardo
Contributor
Contributor
Author

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

daanciorea
Partner - Contributor III
Partner - Contributor III

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
Creator III
Creator III

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.