topic Re: group by order by in QlikView

group by order by

Tue, 14 Oct 2014 18:22:16 GMT

1) what's wrong with this statement. I keep getting expression error.

table:

LOAD ActNo,

count(ActNo) as Uniq,

firstname,

lastname,

expyear,

expmonth,

expday

from a qvd

GROUP BY ActNo order by ActNo, expmonth, expyear desc;

2) if I am just looking into grouping by something without actually doing any aggregation how can I perform group by and then order by.

Re: group by order by

maxgro — Tue, 14 Oct 2014 18:38:33 GMT

you have to add to group by all fields excluding

count(ActNo) as Uniq,

and you have to remove order by

when you group, all fields not in group by need an aggregation function

Re: group by order by

ashfaq_haseeb — Tue, 14 Oct 2014 18:45:11 GMT

Groupby will not work on qvd.

You have ti try like this

Temp:

LOAD ActNo,

firstname,

lastname,

expyear,

expmonth,

expday

from a qvd ;

Main

LOAD

ActNo,

count(ActNo) as Uniq,

only(firstname) as firstname,

only(lastname) as lastname,

only(expyear) as expyear,

only(expmonth) as expmonth,

only(expday) as expday

resident Temp

GROUP BY ActNo;

drop table Temp;

Regards

ASHFAQ

Re: group by order by

robert99 — Tue, 14 Oct 2014 18:48:01 GMT

So this would be required I think

table:

LOAD ActNo,

//count(ActNo) as Uniq,

firstname,

lastname,

expyear,

expmonth,

expday

from a qvd ;

table2:

noconcatenate

LOAD ActNo,

//count(ActNo) as Uniq,

firstname,

lastname,

expyear,

expmonth,

expday

resident table

order by ActNo, expmonth, expyear desc; (order descending?)

drop table;

table2:

join table2 //if required

LOAD ActNo,

count(ActNo) as Uniq

resident table2

GROUP BY ActNo;

Re: group by order by

Anonymous — Tue, 14 Oct 2014 18:53:30 GMT

Hi,

The count function is not valid for the script.

regards!

Re: group by order by

Tue, 14 Oct 2014 19:05:03 GMT

then whats wrong with this statement.

temp:

LOAD

ActNo,

firstname,

lastname,

year,

month

FROM

qvd

order by ActNo,year desc;

Re: group by order by

robert99 — Tue, 14 Oct 2014 19:07:26 GMT

order by can only be done from a resident load (I think)

Re: group by order by

ashfaq_haseeb — Tue, 14 Oct 2014 19:09:43 GMT

Check my reply

Re: group by order by

maxgro — Tue, 14 Oct 2014 19:13:38 GMT

remove order by, you can only use order by in a resident load, not in a load from a qvd

temp:

LOAD

ActNo,

firstname,

lastname,

year,

month

FROM

qvd

Re: group by order by

Tue, 14 Oct 2014 19:39:05 GMT

Thanks everyone. I got it to work but not getting results i want. Here is my data and what i require.

AcctNo Orderdate Expdate item

123 18-02-2014 21-02-2015 5

128 4-5-2014 6-6-2015 32

125 20-02-2014 4-4-2015 7

123 21-03-2014 21-02-2015 5

125 18-05-2014 4-4-2015 7

128 2-2-2014 5-3-2015 21

how can i load above data sort in such a way where it will only pick records with latest ord & latest exp date.

answer for above will be

AcctNo Orderdate Expdate item

123 18-02-2014 21-02-2015 5

125 18-05-2014 4-4-2015 7

128 4-5-2014 6-6-2015 32

Re: group by order by

maxgro — Tue, 14 Oct 2014 19:47:07 GMT

use peek

SET DateFormat='DD-MM-YYYY';

tmp:

load * inline [

AcctNo , Orderdate , Expdate , item

123 , 18-02-2014 , 21-02-2015 , 5

128 , 4-5-2014 , 6-6-2015 , 32

125 , 20-02-2014 , 4-4-2015 , 7

123 , 21-03-2014 , 21-02-2015 , 5

125 , 18-05-2014 , 4-4-2015 , 7

128 , 2-2-2014 , 5-3-2015 , 21

];

final:

NoConcatenate load

Resident tmp

where Peek(AcctNo) <> AcctNo

order by AcctNo, Orderdate; // here Orderdate or Orderdate desc depending on requirement

drop table tmp;

Re: group by order by

robert99 — Tue, 14 Oct 2014 20:07:21 GMT

why not this entry

123

21-03-2014

21-02-2015

And what if one entry has the latest order date and another the latest expiry date. Which one would you select

Re: group by order by

Tue, 14 Oct 2014 20:26:24 GMT

Hi RJ

that is not possible. 2 records could have latest expiry but 2 order date. not 1 each.

Re: group by order by

Tue, 14 Oct 2014 20:29:30 GMT

Hi Massimo

It works, thanks. Can you please explain the logic.

where Peek(AcctNo) <> AcctNo

order by AcctNo, Orderdate; // here Orderdate or Orderdate desc depending on requirement

drop table tmp;

Re: group by order by

maxgro — Tue, 14 Oct 2014 20:40:26 GMT

peek( 'AcctNo' )

returns the value of AcctNo in the previous record read

where peek(AcctNo) <> AcctNo --> you only keep the record when AcctNo is different from the previous AcctNo

if the table is ordered by AcctNo and Orderdate

1 10-10-2013 this one

1 12-10-2013

1 14-10-2013

2 20-1-2012 this one

2 10-2-2014

if you order by date desc

2 10-2-2014 this one

2 20-1-2012

1 14-10-2013 this one

1 12-10-2013

1 10-10-2013

Re: group by order by

robert99 — Tue, 14 Oct 2014 20:43:13 GMT

maybe one approach would be to load and then do somnething like this

load

AcctNo,

max (Orderdate) as ,

firstsortedvalue (distinct ExpDate, - OrderDate as ,

firstsortedvalue (distinct item, - OrderDate as

resident xxxx group by AccNo;

But this would always pick up the latest order date

DateFormat='DD-MM-YYYY';

tmp:

load * inline [
AcctNo ,    Orderdate      ,   Expdate          ,     item

123    ,       18-02-2014 ,     21-02-2015     ,       5
128    ,        4-5-2014   ,         6-6-2015   ,           32
125    ,        20-02-2014 ,      4-4-2015      ,           7
123    ,        21-03-2014 ,    21-02-2015      ,       5
125    ,        18-05-2014 ,      4-4-2015      ,           7
128    ,        2-2-2014   ,        5-3-2015    ,           21
];

final:

load

AcctNo,

Orderdate,

Expdate,

item,

AcctNo as ACT2

Resident tmp

where not exists (ACT2,AcctNo)

order by AcctNo, Orderdate desc

;

drop table tmp;

DROP FIELD ACT2;

Re: group by order by

robert99 — Wed, 15 Oct 2014 06:39:23 GMT

Thanks

I never knew that peek could do this. That is pick up the last value by one field

I though it was just the last (or 2nd to alst value etc)