how to bring in max date

[diwaskarki]

I have a field in my table called "Completed_Date" that displays data like this : Wed, Jun 29 13:38:37 CDT 2016.

There are other fields in the table like operator, office etc.

An operator can have multiple rows for the same office but with different "Completed_Date". How can I bring in the latest date for an operator if other field value is the same?

It can either be in the script or some settings in the straight table itself. Here is what my table looks like for a better understanding.

Id really appreciate the help.

[Anonymous]

first change the data format.

do resident load as below

load distinct operator,office,max(date)

resd..

group by operator,office

[Anil_Babu_Samineni]

First play with date format and then Load as below

Main:

Load Date(Date#(Completed_Date,'DDD, MMM DD hh:mm:ss TTT YYYY'),'DD-MM-YYYY') as Completed_Date From Table;

Right Join (Main)

Load Max(Completed_Date) as Completed_Date

Resident Main;

[diwaskarki]

Anil ,

That script didn't bring in anything although it ran without any errors. (Very strange).

Im thinking may be I can use the max function if  I could do a substring or something and extract this from my completed_date field: Apr 7 2017

Even better if its in this format 04/02017.

Thanks for your help.

[Anil_Babu_Samineni]

Could be format issue? Will you provide Excel example file to test in my machine? Even better, Inline data with QVW

[diwaskarki]

I created a small test spreadsheet for you :

[Anil_Babu_Samineni]

Image won't help rather please attach in excel

[diwaskarki]

Hello Anil,

I don't see an option to attach files.. sorry about that

[Anil_Babu_Samineni]

If you start reply - there is option called "use advance editor" in the right corner of top in your body message - just click that - After that there is option called attach in the bottom of same body message. From there you can upload the file currently you worked for. And just use ctrl+s or add reply on the left hand side message..

[tamilarasu]

Hi Diwas,

Data:

Load Person,

    New_Office,

    Completed_Date,

    Date#(Subfield(SubField(Completed_Date,', ',-1),' CDT',1),'MMM DD hh:mm:ss') as FormattedDate

From

Source;

Right Join

Load Person,

New_Office,

Max(FormattedDate) as FormattedDate

Resident Data Group by Person, New_Office;

DROP Field FormattedDate;