Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I am getting Invalid expression for some times and sometimes not ;
please advise me , did i miss anything in error table ?
//Following throws NO ERROR
TEMP1:
LOAD * INLINE [
Country, Value, SalesDate
USA, 12, 2013-01-04
USA, 14.5, 2013-02-07
USA, 6.6, 2013-03-03
USA, 4.5, 2013-04-11
USA, 7.8, 2013-05-19
USA, 9.4, 2013-06-22
UK, 11.3, 2013-01-31
UK, 10.1, 2013-02-01
UK, 3.2, 2013-03-21
UK, 5.6, 2013-04-15
UK, 3.9, 2013-05-12
UK, 6.9, 2013-06-06
];
MINMAX:
LOAD
month(date#(SalesDate,'YYYY-MM-DD')) as monthdate
Resident TEMP1;
DROP TABLE TEMP1;
/////FOLLOWING THROWS INVALID EXPRESSIONS
TEMP1:
LOAD * INLINE [
Country, Value, SalesDate
USA, 12, 2013-01-04
USA, 14.5, 2013-02-07
USA, 6.6, 2013-03-03
USA, 4.5, 2013-04-11
USA, 7.8, 2013-05-19
USA, 9.4, 2013-06-22
UK, 11.3, 2013-01-31
UK, 10.1, 2013-02-01
UK, 3.2, 2013-03-21
UK, 5.6, 2013-04-15
UK, 3.9, 2013-05-12
UK, 6.9, 2013-06-06
];
MINMAX:
LOAD
month(date#(SalesDate,'YYYY-MM-DD')) as monthdate,
Max(date#(SalesDate,'YYYY-MM-DD')) as maxdate
Resident TEMP1;
DROP TABLE TEMP1 ;
Yes, it does seem to work
TEMP1:
LOAD * INLINE [
Country, Value, SalesDate
USA, 12, 2013-01-04
USA, 14.5, 2013-02-07
USA, 6.6, 2013-03-03
USA, 4.5, 2013-04-11
USA, 7.8, 2013-05-19
USA, 9.4, 2013-06-22
UK, 11.3, 2013-01-31
UK, 10.1, 2013-02-01
UK, 3.2, 2013-03-21
UK, 5.6, 2013-04-15
UK, 3.9, 2013-05-12
UK, 6.9, 2013-06-06
];
MINMAX:
LOAD Month(Date#(SalesDate,'YYYY-MM-DD')) as monthdate,
Date(Max(Date#(SalesDate,'YYYY-MM-DD'))) as maxdate
Resident TEMP1
Group By Month(Date#(SalesDate,'YYYY-MM-DD'));
DROP Table TEMP1;
Hi,
All non-aggregated field names should be in group by clause. Try below
MINMAX:
LOAD
month(date#(SalesDate,'YYYY-MM-DD')) as monthdate,
Max(date#(SalesDate,'YYYY-MM-DD')) as maxdate
Resident TEMP1 Group by SalesDate;
DROP TABLE TEMP1 ;
Second one throws error because you have another field in your resident load which need to be added as Group By as well. If you won't add the group by it will throw error.
MINMAX:
LOAD Month(date#(SalesDate,'YYYY-MM-DD')) as monthdate,
Max(date#(SalesDate,'YYYY-MM-DD')) as maxdate
Resident TEMP1;
DROP TABLE TEMP1
Group By Month(date#(SalesDate));
Tamil - Does Group By allows for using functions?
why don't you just using below without Group by
MINMAX:
LOAD
month(Max(date#(SalesDate,'YYYY-MM-DD'))) as monthdate,
Max(date#(SalesDate,'YYYY-MM-DD')) as maxdate
Resident TEMP1;
DROP TABLE TEMP1 ;
I guess it depends on the requirement. Does the OP need the month of max date or max date for each month
UPDATE: Your's is doing the 1st -> Finding the Month of Max Date
Me (I tried, not sure if it will work) and Tamil proposed Max Date for each Month
That only he can say. We can just give options
Agreed - I just wanted to point out the differences so that OP can have an easy time deciding
galax_allu what is your requirement? what output you need for this data? Actually this question we should have asked earlier
Yes. We can use functions. I guess, yours is correct. If we want to show max date for each month, we have to use Month(Date#(SalesDate)) in group by clause. So, final output will be max date for each month. If we use SalesDate as group by clause then max date will be all the sales date in final table. I can confirm, if you could do a small test?
Sorry for the late reply. Its really hard to type reply from my mobile. (It takes very long time. Many times OP marked the correct answer before I post the reply) So, I just avoid posting to the questions, when i'm at home. Because I can't reply immediately.