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.
1)
you have to add to group by all fields excluding
and you have to remove order by
2)
when you group, all fields not in group by need an aggregation function
Groupby will not work on qvd.
You have ti try like this
Temp:
from a qvd ;
Main
LOAD
ActNo,
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
So this would be required I think
//count(ActNo) as Uniq,
table2:
noconcatenate
resident table
order by ActNo, expmonth, expyear desc; (order descending?)
drop table;
join table2 //if required
count(ActNo) as Uniq
resident table2
Hi,
The count function is not valid for the script.
regards!
then whats wrong with this statement.
temp:
year,
month
FROM
qvd
order by ActNo,year desc;
order by can only be done from a resident load (I think)
Check my reply
remove order by, you can only use order by in a resident load, not in a load from a qvd
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
