unique value or group by problem

[#1]

Hello,

I would like to create a table with id and Create_Date from source which has many lines with the same id but differents statut with a date (Change_date).

So I want only the Change_date from the 'new' statut line for creating Create_date in my new table.

When I do like below, the new table has got two lines for each id, one with the Create_date, and another one with a blank in Create_date.

Mantis:

LOAD

  distinct id,

  if(Statut='New',Change_Date) as Create_Date ,

FROM



(ooxml, embedded labels, table is Données);

The table 'Données' is like this :

Id, Statut, Change_date

11,New,01/01/2012

11,open,10/01/2012

11,Closed,20/01/2012

31,Open,11/01/2012

31,New, 10/01/2012

...

And the result is like this :

id, Create_Date

11, 01/01/2012

11, -

31, 10/01/2012

31, -

I don't want the lines with blank date. I tried to put Group by id, but it didin't work.

Any idea for helping me

Please

Stéphane

[swuehl · Accepted solution]

Hi Stephane,

use a where clause in your load to limit the records returned, something like

LOAD distinct

id,

Change_Date as Create_Date

from TABLE where Statut='New';

[#3]

Thanks swuehl;

My problem is over

stéphane