Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
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.