Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Rajesh
Creator II
Creator II

Need Max date field in load script

Hi All,

I am trying to get max(OpenDate) from a table in resident load, but not getting its showing error like invalid expression

any suggestion plz. OpenDate format is in  : 02-08-2017

See below screen shot.

Capture.PNG

Thanks in advance

Rajesh

7 Replies
techvarun
Specialist II
Specialist II

Hi,

Load it in a temp table and store the result in a Variable.

Temp:

LOAD

     Min(DateFieldName) as MinDate,

     Max(DateFieldName) as MaxDate

Resident TableName;

Refer the below thread:

MIN and MAX Date in Load Script | Qlik Community

its_anandrjs

Hi,

I thing you forget to use Group By in script

Load *,

Max(OpenDate) as CurrentDate

Resident Incident

Group By

Your all fields here that you load in this table ;

Ex:-

Load

ID,

Max(OpenDate) as CurrentDate

Resident Incident

Group By ID;


Regards,

Anand

Rajesh
Creator II
Creator II
Author

Thanks Varun for u r immidiate responce,

my load statement like

Load *,

Max(OpenDate) as CurrentDate

Resident Incident;

but it showing invalid expression error

its_anandrjs

In Incident how many fields are there just check and use them in your Group By statement.


Regards

Anand

bohravanraj
Partner - Creator II
Partner - Creator II

Hi Rajesh,

You Forgot to group use group by.

Load *,

Max(OpenDate) as CurrentDate

Resident Incident;

In Your below Script, You need to mention alll the field which is there in *.

Please revert for more Clarity and if it is not resolved.


Regards,

Vanraj Dinesh Bohra

effinty2112
Master
Master

Hi Rajesh,

Here's another method,

MaxOpenDate:

Load max(Temp) as MaxOpenDate;

LOAD FieldValue('OpenDate',IterNo()) as Temp

AutoGenerate 1 While not IsNull(FieldValue('OpenDate',IterNo()));

Let vMaxOpenDate = Floor(Peek('MaxOpenDate',0,'MaxOpenDate'));

DROP Table MaxOpenDate;

If you have a large dataset from which to obtain your max date you will find this will run quicker because rather than go through maybe millions of lines this code goes through the distinct field values of OpenDate which will number maybe only a few hundred.

Cheers

Andrew

Rajesh
Creator II
Creator II
Author

Thank you All for u r valid suggestions

i was looking for NoOfDates between Current  and previous date

done by using precising load ---Floor(Today()- OpenDate) as Days

Regard

Rajesh