doubt about group by clause

[dcd123456]

hello

i have an excel file with 6 fields Date, OrderN, SEQ, STATUS, DateRealease and Time,  for example:

Date	OrderN	SEQ	Status	DateRelease	Time	13/04/2013200000000430514/06/201312:03:28
13/04/2013	2000000004	31	6	14/06/2013	13:11:56
13/04/2013	2000000004	32	7	14/06/2013	13:23:06
13/04/2013	2000000004	33	9	21/06/2013	15:03:18
13/04/2013	2000000004	34	9	25/06/2013	11:58:08
13/04/2013	2000000004	35	99	25/06/2013	12:43:09
13/04/2013	2000000004	36	98	26/06/2013	7:24:00
14/04/2014	2000000000	1	0	04/03/2013	12:53:31
14/04/2014	2000000000	2	2	20/03/2013	11:35:25
14/04/2014	2000000000	3	9	20/03/2013	12:39:02
14/04/2014	2000000000	4	80	20/03/2013	12:59:10
14/04/2014	2000000000	5	4	20/03/2013	12:59:12
14/04/2014	2000000000	6	9	20/03/2013	13:23:07
14/04/2014	2000000000	7	9	20/03/2013	13:40:49

I need group the rows byt the two first field, Date and OrderN and take only for each pair (Date,OrderN) take the rows with max value of SEQ field and the row with min value of the SEQ field, and for this rows also take the fields Status DateRelease and Time, for example in the above example i need to take the rows:

13/04/2013

2000000004

31

6

14/06/2013

13:11:56

13/04/2013

2000000004

36

98

26/06/2013

7:24:00

and

14/04/2014

2000000000

1

0

04/03/2013

12:53:31

14/04/2014

2000000000

7

9

20/03/2013

13:40:49

first i think to use the group by clause but i don't know how to take the min and the max SEQ values and the others fields of theese rows.

anybody know how to do this?

i have attached and example

thanks in advance for your help

regards

diego

[#2]

Hello.
I attached example with using function FirstSortedValue().
Another approach is using Left Join after two 'group by load'.
I hope it helps.

[MK_QSL]

Use below script...

Temp:

Load

  *,

  Date&OrderN as Key

Inline

[

  Date, OrderN, SEQ, Status, DateRelease, Time

  13/04/2013, 2000000004, 31, 6, 14/06/2013, 13:11:56

  13/04/2013, 2000000004, 32, 7, 14/06/2013, 13:23:06

  13/04/2013, 2000000004, 33, 9, 21/06/2013, 15:03:18

  13/04/2013, 2000000004, 34, 9, 25/06/2013, 11:58:08

  13/04/2013, 2000000004, 35, 99, 25/06/2013, 12:43:09

  13/04/2013, 2000000004, 36, 98, 26/06/2013, 7:24:00

  14/04/2014, 2000000000, 1, 0, 04/03/2013, 12:53:31

  14/04/2014, 2000000000, 2, 2, 20/03/2013, 11:35:25

  14/04/2014, 2000000000, 3, 9, 20/03/2013, 12:39:02

  14/04/2014, 2000000000, 4, 80, 20/03/2013, 12:59:10

  14/04/2014, 2000000000, 5, 4, 20/03/2013, 12:59:12

  14/04/2014, 2000000000, 6, 9, 20/03/2013, 13:23:07

  14/04/2014, 2000000000, 7, 9, 20/03/2013, 13:40:49

];

NoConcatenate

T2:

Load Min(SEQ) as SEQ Resident Temp Group By Key;

Load Max(SEQ) as SEQ Resident Temp Group By Key;

Inner Join (T2)

Load * Resident Temp;

Drop Table Temp;

[dcd123456]

Hello

thanks very much for your answer, only one question, how can i supress the rows in the table ST_ORDER_GROUP  that have null value in the field SEQ.

thanks

regards

diego

[#5]

There are not Null values in this table, if you make table box it show all possible values for all fields you have added to it, thats why you see null values in MAXSEQ column.

You can just mark option 'Omit Rows where Field is NULL' for MAXSEQ field.

Or if you want to remain just groupped rows you should drop table ST_ORDER.

[bertdijks]

HI,

See attached example. I added used the exists funtion to only select the order with the min and max seq.

LOAD Date,

     OrderN,

     SEQ,

     Status,

     DateRelease,

     Time

FROM

ej_order.xls

(biff, embedded labels, table is Hoja1$);









tmp_ST_ORDER_GROUP:

LOAD Date as datetmp,

     OrderN as orderNtmp,

   //  min(SEQ ) as minSEQ,

//    max(SEQ ) as maxSEQ,

     num(Date)&'-'&OrderN&'-'&min(SEQ ) as minSEQ2,

     num(Date)&'-'&OrderN&'-'&max(SEQ ) as maxSEQ2

// how to take the next fiels also????    

//     min(SEQ ) as MAXSEQ

//     Status,

//     DateRelease,

//     Time

Resident ST_ORDER Group by Date, OrderN;



TRACE  ST_ORDER_GROUP;

NoConcatenate

ST_ORDER_GROUP:

Load

Date,

     OrderN,

     SEQ,

     Status,

     DateRelease,

     Time

Resident ST_ORDER

where Exists (minSEQ2,Date&'-'&OrderN&'-'&SEQ) or Exists (maxSEQ2,Date&'-'&OrderN&'-'&SEQ);

;



  DROP table tmp_ST_ORDER_GROUP;

DROP table ST_ORDER;

Bert