Invalid expression Error for different statments..

[Anonymous]

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 ;

[tamilarasu]

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 ;

[sunny_talwar]

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));

[sunny_talwar]

Tamil - Does Group By allows for using functions?

[Kushal_Chawda]

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 ;

[sunny_talwar]

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

[Kushal_Chawda]

That only he can say. We can just give options

[sunny_talwar]

Agreed - I just wanted to point out the differences so that OP can have an easy time deciding

[Kushal_Chawda]

galax_allu what is your requirement? what output you need for this data? Actually this question we should have asked earlier

[tamilarasu]

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.