topic Re: Max of a Date - in LOAD Script in QlikView

Max of a Date - in LOAD Script

dmohanty — Fri, 27 Feb 2015 05:47:13 GMT

Hi All,

I have a data set like this below:

I want to write LOAD script, so that it would fetch the Max(CONFIRM_CREATE_DTE) and I will have one record: 02/14/2012.

I have already tried using MAX() and First Sorted Value, but in vain.

Could someone please help here?

Re: Max of a Date - in LOAD Script

jyothish8807 — Fri, 27 Feb 2015 05:49:40 GMT

Hi Dmohanthy,

Try like this:

let Var1=Max(CONFIRM_CREATE_DTE);

Load *

from table name

where CONFIRM_CREATE_DTE= $(Var1),

Regards

Re: Max of a Date - in LOAD Script

CELAMBARASAN — Fri, 27 Feb 2015 05:50:04 GMT

Did you tried like this?

Load

Max(CONFIRM_CREATE_DTE) AS MAXCONFIRM_CREATE_DTE

Resident TableName;

Re: Max of a Date - in LOAD Script

dmohanty — Fri, 27 Feb 2015 05:58:41 GMT

Hi Adhimulam,

I tried like this as well, but it didn't work

Re: Max of a Date - in LOAD Script

dmohanty — Fri, 27 Feb 2015 06:01:43 GMT

It is not working either

Re: Max of a Date - in LOAD Script

anbu1984 — Fri, 27 Feb 2015 06:21:37 GMT

What is your source? What result are you getting for the above solutions?

Re: Max of a Date - in LOAD Script

jyothish8807 — Fri, 27 Feb 2015 06:28:44 GMT

Check your date format,

try to make it:

let Var1=Max(CONFIRM_CREATE_DTE);

load

date(CONFIRM_CREATE_DTE,'MM/DD/YYYY') as CONFIRM_CREATE_DTE

from table

where CONFIRM_CREATE_DTE= $(Var1),

Regards

Re: Max of a Date - in LOAD Script

CELAMBARASAN — Fri, 27 Feb 2015 06:30:12 GMT

What is the result you are getting?

If you don't get any,then this means your date field is not formatted as date field. It considers the field data type as text.

You need to convert this

Load

Date(Max(Date(Date#(CONFIRM_CREATE_DTE, 'MM/DD/YYYY'))) AS MAXCONFIRM_CREATE_DTE

Resident TableName;

Re: Max of a Date - in LOAD Script

Fri, 27 Feb 2015 06:35:00 GMT

Try Like this....It is working

Re: Max of a Date - in LOAD Script

praveenak — Mon, 02 Mar 2015 15:38:16 GMT

HI,

Try this way,

Here is a sample approach,

table1:

LOAD Number,

ID,

Date_1

FROM

(ooxml, embedded labels, table is Sheet1);

table2:

NoConcatenate

LOAD Number,ID,Date(Max(Date(Date#(Date_1, 'MM/DD/YYYY')))) as MDate

Resident table1;

DROP table1;

This is working fine..

At the time u load data..u cant use MAX()(which means u r aggregating at the time fetching), because the script executes sequentially and thus Max() can be calculated only the after is fetched from source......so follow the way as above.

Re: Max of a Date - in LOAD Script

linusblomberg — Mon, 02 Mar 2015 15:48:33 GMT

Depending on your data amount I would add an extra step for performance otherwise you can skip TempDates.

TempDates:

Load distinct CONFIRM_CREATE_DTE

resident YOURTABLE;

VarTemp:

LOAD

Max(CONFIRM_CREATE_DTE) as MaxDate

Resident TempDates;

LET vMaxDate = peek('MaxDate');

Drop Tables TempDates,VarTemp;

Now you have on variable with your value. if you want it in your table do a resident load again and add your variable.