Problem with Group By Sentence

[#1]

Hi all,

I'm having a problem with a simple group by, and I can't guess why.

I have a text file with a few records, and I need to load just the newest records for each value

I'm trying this but doesn't work.

Seguimiento:

LOAD

  %KEY_Incidente_Seguimiento,

  [Comentario Gerencia],

  [Status Follow Up],

  Date([Target Date]) AS [Target Date],

  Modificado as FechaModificacion

FROM [$(APP)\QVD\Seguimiento.txt]

(txt, utf8, embedded labels, delimiter is ';', msq)

;

Load

%KEY_Incidente_Seguimiento,

FechaModificacion

Resident Seguimiento

Where Floor(FechaModificacion) = Max(Floor(FechaModificacion))

Group by %KEY_Incidente_Seguimiento;

Hope you can help me!

Thanks!

[MK_QSL]

Is FechaModificacion a timestamp?

[jyothish8807 · Accepted solution]

hi Jonathan,

try like this:

Load

%KEY_Incidente_Seguimiento,

Max(Floor(FechaModificacion)) as ABC

Resident Seguimiento

Group by %KEY_Incidente_Seguimiento;

regards

KC

[#4]

This value comes from a Qlik chart. and then stored into a txt.

I set the field by this way:

FechaModificacion = Today();

So the format shown is = "20/10/2014 12:28:30 PM" , when I make a Floor(FechaModificacion) I get the numeric value used by Qlik to manage dates. I show it into a Text box and y see 41932, so it works fine.

[Peter_Cammaert]

In a load with GROUP BY clause, you cannot list columns that don't appear in the GROUP BY clause. They must to be used in an aggregation function, like:

Load

%KEY_Incidente_Seguimiento,

Max(FechaModificacion) AS FechaModificacion

Resident Seguimiento

Group by %KEY_Incidente_Seguimiento;

This will build a table with the last modification date/time for every incident.

[Peter_Cammaert]

Use the FirstSortedValue() aggregation function to lift other fields of the most recent record.

[MK_QSL]

What is your objective to use this code? Can you please clarify little more... IF possible, pls provide some sample data...

[#8]

Thanks this works perfectly, i'm sure i tried it but I made a mistake I guess.

TY again.

Regards!