Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
why not this entry
5
And what if one entry has the latest order date and another the latest expiry date. Which one would you select
Hi RJ
that is not possible. 2 records could have latest expiry but 2 order date. not 1 each.
Hi Massimo
It works, thanks. Can you please explain the logic.
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 10-10-2013
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
OR
DateFormat='DD-MM-YYYY';
load * inline [AcctNo , Orderdate , Expdate , item123 , 18-02-2014 , 21-02-2015 , 5128 , 4-5-2014 , 6-6-2015 , 32125 , 20-02-2014 , 4-4-2015 , 7123 , 21-03-2014 , 21-02-2015 , 5125 , 18-05-2014 , 4-4-2015 , 7128 , 2-2-2014 , 5-3-2015 , 21];
Orderdate,
Expdate,
item,
AcctNo as ACT2
where not exists (ACT2,AcctNo)
order by AcctNo, Orderdate desc
;
DROP FIELD ACT2;
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)
