Max of a Date - in LOAD Script

[dmohanty]

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?

[jyothish8807]

Hi Dmohanthy,

Try like this:

let Var1=Max(CONFIRM_CREATE_DTE);

Load *

from table name

where CONFIRM_CREATE_DTE= $(Var1),

Regards

KC

[CELAMBARASAN]

Did you tried like this?

Load

     Max(CONFIRM_CREATE_DTE) AS MAXCONFIRM_CREATE_DTE

Resident TableName;

[dmohanty]

Hi Adhimulam,

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

[dmohanty]

It is not working either

[anbu1984]

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

[jyothish8807]

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

KC

[CELAMBARASAN]

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;

[#9]

Try Like this....It is working

[praveenak]

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.