Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
cacosta35
Creator II
Creator II

MIN and MAX Date in Load Script

Hello!

Could someone help me show me how to add 2 new fields for the MIN and MAX  of the field QtrDates... I'm trying to get the MIN and MAX dates of the Current Quarter (QtrDates)... I tried loading it but it keeps saying invalid function... I just don't know exactly which part of the scrpt  should I insert this.

You may want to look at the attached file to a better undestanding...

Tks.

1 Solution

Accepted Solutions
jagan
MVP & Luminary
MVP & Luminary

Hi,

Try like this

Temp:

LOAD

     Min(DateFieldName) as MinDate,

     Max(DateFieldName) as MaxDate

Resident TableName;

LET vMinDate = floor(peek('MinDate'));

LET vMaxDate = floor(peek('MaxDate'));

Drop Table TableName;

Now the two variables vMinDate and vMaxDate has min and max dates respectively.

Hope this helps you.

Regards,

Jagan.

View solution in original post

8 Replies
Not applicable

Hi,

You can use the below script for calculating the minimum and the maximum date of QtrDates and store this results in variables.This result stored in variable can be used whenever required.

Temp:

LOAD min(QtrDates) as MinDate

Resident CurrentQtr;

Temp1:

LOAD max(QtrDates) as MaxDate

Resident CurrentQtr;

LET vMinDate = num(floor(peek(MinDate,0,Temp)));

LET vMaxDate = num(floor(peek(MaxDate,0,Temp1)));

Drop Table Temp,Temp1;

Hope this helps,

Regards,

Snehal Nabar

Not applicable

Temp:

LOAD *,

min(QtrDates) as MinDate,

max(QtrDates) as MaxDate

Resident CurrentQtr    Group by fieldname;

Drop Table CurrentQtr;

Not applicable

Hi vishwaranjan,

Your script will return the erro r: invalid expression.

Regards,

Snehal Nabar

Not applicable

Hi vishwaranjan,

Your script will return the error: invalid expression.

Regards,

Snehal Nabar

cacosta35
Creator II
Creator II
Author

Snehal,

I tried this but I got this error message...pls. take note that the QtrDates came from a joint table...Does it have someting to do with it?

Field not found - <QtrDates>

Temp:

LOAD min(QtrDates) as MinDate

Resident CurrentQtr

Not applicable

Hi,

Check whether CurrentQtr table is loaded before the above mentioned script.

As Temp table uses resident load to load data from CurrentQtr table to  perform calculations.

Regards,

Snehal Nabar

jagan
MVP & Luminary
MVP & Luminary

Hi,

Try like this

Temp:

LOAD

     Min(DateFieldName) as MinDate,

     Max(DateFieldName) as MaxDate

Resident TableName;

LET vMinDate = floor(peek('MinDate'));

LET vMaxDate = floor(peek('MaxDate'));

Drop Table TableName;

Now the two variables vMinDate and vMaxDate has min and max dates respectively.

Hope this helps you.

Regards,

Jagan.

View solution in original post

cacosta35
Creator II
Creator II
Author

Snehal,

Oh!  i got it  now,,... I forgot to name a new table when I did a left join.  This work perfectly now... However, how come the results  showed up as numbers when my  Qtrdates field is already in date format?  I tried using the DATE# and Date function but to no avail. 

Thanks