topic Re: Need Max date field in load script in QlikView

Need Max date field in load script

Rajesh — Wed, 25 Nov 2020 16:16:04 GMT

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.

Thanks in advance

Rajesh

Re: Need Max date field in load script

techvarun — Fri, 11 Aug 2017 06:09:12 GMT

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

Re: Need Max date field in load script

its_anandrjs — Fri, 11 Aug 2017 06:14:26 GMT

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

Re: Need Max date field in load script

Rajesh — Fri, 11 Aug 2017 06:17:31 GMT

Thanks Varun for u r immidiate responce,

my load statement like

Load *,

Max(OpenDate) as CurrentDate

Resident Incident;

but it showing invalid expression error

Re: Need Max date field in load script

its_anandrjs — Fri, 11 Aug 2017 06:33:16 GMT

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

Regards

Anand

Re: Need Max date field in load script

bohravanraj — Fri, 11 Aug 2017 06:52:34 GMT

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

Re: Need Max date field in load script

effinty2112 — Fri, 11 Aug 2017 07:43:34 GMT

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

Re: Need Max date field in load script

Rajesh — Fri, 11 Aug 2017 10:00:02 GMT

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