topic Re: Min and Max not working for dates in QlikView

Min and Max not working for dates

Anonymous — Tue, 26 Apr 2016 09:50:11 GMT

Hi,

I am unable to get the variables fdate and tdate in to the CALENDAR objects ..

Is there any syntax error ?

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

UK, 5.6, 2013-04-15

UK, 3.9, 2013-05-12

UK, 6.9, 2013-06-06

];

Let fdate = MIN(date#(SalesDate, 'YYYY-MM-DD'));

LET tdate = Max(date#(SalesDate, 'YYYY-MM-DD'));

Re: Min and Max not working for dates

sunny_talwar — Tue, 26 Apr 2016 16:07:57 GMT

You cannot do it directly, you need to have a resident load to calculate the Min and Max Date and then use Peek() function to get the values

Re: Min and Max not working for dates

sunny_talwar — Tue, 26 Apr 2016 16:09:58 GMT

May be like this:

Table:

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

UK, 5.6, 2013-04-15

UK, 3.9, 2013-05-12

UK, 6.9, 2013-06-06

];

MinMax:

LOAD Min(Date#(SalesDate, 'YYYY-MM-DD')) as MinDate,

Max(Date#(SalesDate, 'YYYY-MM-DD')) as MaxDate

Resident Table;

Let fdate = Peek('MinDate');

LET tdate = Peek('MaxDate');

Re: Min and Max not working for dates

trdandamudi — Tue, 26 Apr 2016 16:14:56 GMT

Try like below:

Please note one of the row as below has wrong date:

UK, 3.2, 2013-03-2182 <---- Wrong date

Data:

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

UK, 5.6, 2013-04-15

UK, 3.9, 2013-05-12

UK, 6.9, 2013-06-06

];

Temp:

Load min(SalesDate) as min_SalesDate,

max(SalesDate) as max_SalesDate

Resident Data;

Let fdate = Peek('min_SalesDate');

LET tdate = Peek('max_SalesDate');

Re: Min and Max not working for dates

Anonymous — Wed, 27 Apr 2016 06:46:57 GMT

thanks

Re: Min and Max not working for dates

Anonymous — Wed, 27 Apr 2016 06:47:21 GMT

thanks