topic Re: Scripting help in QlikView

Scripting help

alec1982 — Thu, 13 Dec 2012 17:35:32 GMT

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

Re: Scripting help

CELAMBARASAN — Thu, 13 Dec 2012 17:45:46 GMT

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;

Re: Scripting help

alec1982 — Thu, 13 Dec 2012 17:46:38 GMT

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,