Set a FINAL month flag for each account during load

[#1]

Hi All,

I have some data that looks like this:

Account	Due Date
AZ0003	05/06/2010
AZ0003	05/07/2010
AZ0003	05/08/2010
AZ0003	05/09/2010
AZ0003	05/10/2010
AZ0003	05/11/2010
ZZ0099	25/06/2010
ZZ0100	25/07/2010
ZZ0101	25/08/2010
ZZ0102	25/09/2010
XD0033	14/06/2010
XD0034	14/07/2010
XD0035	14/08/2010
XD0036	14/09/2010
XD0037	14/10/2010
XD0038	14/11/2010

During the load is there a best practice way to identify the final month for each account and set the status to FINAL for the last record and LIVE for the others, so the data would look like this after the load:

Account	Due Date	Status
AZ0003	05/06/2010	LIVE
AZ0003	05/07/2010	LIVE
AZ0003	05/08/2010	LIVE
AZ0003	05/09/2010	LIVE
AZ0003	05/10/2010	LIVE
AZ0003	05/11/2010	FINAL
ZZ0099	25/06/2010	LIVE
ZZ0100	25/07/2010	LIVE
ZZ0101	25/08/2010	LIVE
ZZ0102	25/09/2010	FINAL
XD0033	14/06/2010	LIVE
XD0034	14/07/2010	LIVE
XD0035	14/08/2010	LIVE
XD0036	14/09/2010	LIVE
XD0037	14/10/2010	LIVE
XD0038	14/11/2010	FINAL

I guess I could sort the data by account ascending and by date descending then use peek, so if that if account<>peek(account) then FINAL else LIVE... but just wondered if there's a better way?

Many thanks,
Phil

[#2 · Accepted solution]

Hi,

Check the attached application.

Your script should be as follow.

Test:
Load Account, date(date#([Due Date],'DD/MM/YYYY')) as Date,left(Account,2) as Account_new;
Load * Inline [
Account, Due Date
AZ0003, 05/06/2010
AZ0003, 05/07/2010
AZ0003, 05/08/2010
AZ0003, 05/09/2010
AZ0003, 05/10/2010
AZ0003, 05/11/2010
ZZ0099, 25/06/2010
ZZ0100, 25/07/2010
ZZ0101, 25/08/2010
ZZ0102, 25/09/2010
XD0033, 14/06/2010
XD0034, 14/07/2010
XD0035, 14/08/2010
XD0036, 14/09/2010
XD0037, 14/10/2010
XD0038, 14/11/2010
];
Left Join
Load Account_new,
 date(Max(Date)) as Date,
 'FINAL' as Status
Resident Test
group by Account_new;
Noconcatenate
Test1:
Load Account,
 Date,
 Account_new,
 if(len(Status)<=0,'LIVE',Status) as Status
Resident Test;
Drop Table Test;

Hope this may help you.

-Peterson

[#3]

Hi Kevin,

Thank you very much for that, it does exactly what I need... I was working towards loading the max date by account and joining it but your solution does everything much more efficiently.

Many thanks,
Phil