topic Re: Invalid expression Error for different statments.. in QlikView

Invalid expression Error for different statments..

Anonymous — Fri, 06 May 2016 13:05:38 GMT

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 ;

Re: Invalid expression Error for different statments..

tamilarasu — Fri, 06 May 2016 16:00:10 GMT

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..

sunny_talwar — Fri, 06 May 2016 16:00:47 GMT

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..

sunny_talwar — Fri, 06 May 2016 16:05:48 GMT

Tamil - Does Group By allows for using functions?

Re: Invalid expression Error for different statments..

Kushal_Chawda — Fri, 06 May 2016 16:11:05 GMT

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..

sunny_talwar — Fri, 06 May 2016 16:13:02 GMT

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..

Kushal_Chawda — Fri, 06 May 2016 16:14:38 GMT

That only he can say. We can just give options

Re: Invalid expression Error for different statments..

sunny_talwar — Fri, 06 May 2016 16:15:41 GMT

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..

Kushal_Chawda — Fri, 06 May 2016 16:19:08 GMT

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..

tamilarasu — Fri, 06 May 2016 16:47:41 GMT

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.

Re: Invalid expression Error for different statments..

sunny_talwar — Fri, 06 May 2016 16:51:12 GMT

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;