topic Re: MIN and MAX Date in Load Script in QlikView

MIN and MAX Date in Load Script

Anonymous — Thu, 25 Apr 2013 22:51:06 GMT

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.

Re: MIN and MAX Date in Load Script

Fri, 26 Apr 2013 06:45:30 GMT

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

Re: MIN and MAX Date in Load Script

Fri, 26 Apr 2013 06:56:02 GMT

Temp:

LOAD *,

min(QtrDates) as MinDate,

max(QtrDates) as MaxDate

Resident CurrentQtr Group by fieldname;

Drop Table CurrentQtr;

Re: MIN and MAX Date in Load Script

Fri, 26 Apr 2013 07:22:16 GMT

Hi vishwaranjan,

Your script will return the erro r: invalid expression.

Regards,

Snehal Nabar

Re: MIN and MAX Date in Load Script

Fri, 26 Apr 2013 07:22:23 GMT

Hi vishwaranjan,

Your script will return the error: invalid expression.

Regards,

Snehal Nabar

Re: MIN and MAX Date in Load Script

Anonymous — Fri, 26 Apr 2013 16:49:30 GMT

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

Re: MIN and MAX Date in Load Script

Mon, 29 Apr 2013 12:55:45 GMT

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

Re: MIN and MAX Date in Load Script

jagan — Mon, 29 Apr 2013 14:24:38 GMT

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.

Re: MIN and MAX Date in Load Script

Anonymous — Tue, 30 Apr 2013 19:17:03 GMT

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