Min and Max not working for dates

[Anonymous]

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

[sunny_talwar · Accepted solution]

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

[sunny_talwar]

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

[trdandamudi]

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

[Anonymous]

thanks

[Anonymous]

thanks