topic doubt about group by clause in QlikView

doubt about group by clause

dcd123456 — Mon, 14 Apr 2014 06:15:25 GMT

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

14/06/2013

13:11:56

13/04/2013

2000000004

26/06/2013

7:24:00

and

14/04/2014

2000000000

04/03/2013

12:53:31

14/04/2014

2000000000

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

Re: doubt about group by clause

Mon, 14 Apr 2014 06:38:45 GMT

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

Re: doubt about group by clause

MK_QSL — Mon, 14 Apr 2014 06:59:49 GMT

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;

Re: doubt about group by clause

dcd123456 — Mon, 14 Apr 2014 07:01:54 GMT

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

Re: doubt about group by clause

Mon, 14 Apr 2014 07:15:26 GMT

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.

Re: doubt about group by clause

bertdijks — Mon, 14 Apr 2014 08:05:34 GMT

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