Scripting help

[alec1982]

Hi Guys,

I have the following table:

ID	Subtype	DateType	Date
117835		Commencement	3/1/2002
117835		CPI Increase	3/1/2002
117835		Expiration	2/28/2015
117835	Cancellation Option	Notice End	3/1/2005
117835	R of First Refusal	Notice End	2/28/2007
117835	Renewal Option	Notice End	8/31/2010
117835	Termination Option	Notice End	8/31/2009

I need to write a script to show the max date grouped by the ID and Date Type where the DateType ='Notice Date' and then show the value of the Subtype.

so my result table should look like:

117835     Termination Option     Notice End     8/31/2009

Let me know if you can help.

Thxs,

Alec

[CELAMBARASAN · Accepted solution]

Assuming that your the above information in TempTable

TempTable:

Load

*

From....

NoConcatenate//used because of the table below also going to be in same struct

TempTable1:

Load *

Resident

TempTable where DateType='Notice End'

Order by ID,DateType,Date;

Load

ID,

LastValue(SubType) AS SubType,

DateType,

Max(Date) AS MaxDate

Resident

TempTable1

Group by ID, DateType;

Drop table TempTable, TempTable1;

[alec1982]

I guess I am very close:

LeaseOption:

LOAD

    ID,

    Median(Subtype) as [Option Type],

    DateType

     max(Date#(LEFT(Date,10),'YYYY-MM-DD')) as Date

RESIDENT

    KeyDate

where DateType='Notice End'

Group by

    ID, DateType ;

I think the only thing need to be fixed is the highlited line to show the right value.

Thxs,