Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

galax_allu
Valued Contributor

Invalid expression Error for different statments..

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 ;

1 Solution

Accepted Solutions

Re: Invalid expression Error for different statments..

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;

10 Replies

Re: Invalid expression Error for different statments..

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 ;

Re: Invalid expression Error for different statments..

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

Re: Invalid expression Error for different statments..

Tamil - Does Group By allows for using functions?

Re: Invalid expression Error for different statments..

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 ;

Re: Invalid expression Error for different statments..

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

Re: Invalid expression Error for different statments..

That only he can say. We can just give options

Re: Invalid expression Error for different statments..

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

Re: Invalid expression Error for different statments..

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

Re: Invalid expression Error for different statments..

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.

Community Browser