Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
galax_allu
Valued Contributor

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

Re: Min and Max not working for dates

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

5 Replies

Re: Min and Max not working for dates

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

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
Honored Contributor

Re: Min and Max not working for dates

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

galax_allu
Valued Contributor

Re: Min and Max not working for dates

thanks

galax_allu
Valued Contributor

Re: Min and Max not working for dates


thanks

Community Browser