topic Re: Problem with Group By Sentence in QlikView

Problem with Group By Sentence

Mon, 20 Oct 2014 15:22:09 GMT

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!

Re: Problem with Group By Sentence

MK_QSL — Mon, 20 Oct 2014 15:25:04 GMT

Is FechaModificacion a timestamp?

Re: Problem with Group By Sentence

jyothish8807 — Mon, 20 Oct 2014 15:30:46 GMT

hi Jonathan,

try like this:

Load

%KEY_Incidente_Seguimiento,

Max(Floor(FechaModificacion)) as ABC

Resident Seguimiento

Group by %KEY_Incidente_Seguimiento;

regards

Re: Problem with Group By Sentence

Mon, 20 Oct 2014 15:31:34 GMT

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.

Re: Problem with Group By Sentence

Peter_Cammaert — Mon, 20 Oct 2014 15:33:45 GMT

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.

Re: Problem with Group By Sentence

Peter_Cammaert — Mon, 20 Oct 2014 15:35:29 GMT

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

Re: Problem with Group By Sentence

MK_QSL — Mon, 20 Oct 2014 15:36:35 GMT

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

Re: Problem with Group By Sentence

Mon, 20 Oct 2014 15:38:14 GMT

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

TY again.

Regards!