Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Min and Max not working for dates

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

1 Solution

Accepted Solutions
sunny_talwar

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

View solution in original post

5 Replies
sunny_talwar

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
Master II
Master II

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
Not applicable
Author

thanks

Anonymous
Not applicable
Author


thanks