creating a min date, where values repeat

[#1]

Hi

I need to reduce my date to show the minimum date a customer held status

I have a table containg this information,

Customerno FromDate  Status

74  30/04/2015 Purchasing

74  08/05/2015 Moving

74  18/05/2015 Moving

74  26/05/2015 Moving

74  01/06/2015 Purchasing

74  08/06/2015 Purchasing

74  12/06/2015 Purchasing

74  26/06/2015 Purchasing

74  08/07/2015 Purchasing

74  20/07/2015 Purchasing

74  26/07/2015 Purchasing

74  02/08/2015 Purchasing

74  09/08/2015 Purchasing

74  16/08/2015 Purchasing

74  23/08/2015 Purchasing

I need to reduce this to;

Customerno FromDate  Status

74  30/04/2015 Purchasing

74  08/05/2015 Moving

74  01/06/2015 Purchasing

I've currently got the following in the script;

LOAD
Min(FromDate) AS FromDate,
Customerno,
Status
Resident StatusHistory
Group By Customerno, Status;

which was working ok, but now the Status 'Purchasing' is seen twice, using the min date would exclude the change in behaviour on the 01/06/2015..

I think I need to use peek or previous in the script, but i'm not 100% sure how to go about writting this.

Can anyone help please?

Many thanks in advance

[pokassov · Accepted solution]

t1:

LOAD
FromDate,
Customerno,
Status,

if(Previous(Customerno)=Customerno and Previous(Status)=Status,0,1)          As Check
Resident StatusHistory
Group By Customerno, Status, FromDate

Order By Customerno, FromDate, Status;

t2:

load

FromDate,
Customerno,
Status

REsident t1

where Check=1;

[#3]

Heather, The min should work here. I am suspecting your "DateFormat" environment have different data format against source.

So while loading the data, please use Date# function to convert into date or change the DateFormat

SET DateFormat = 'DD/MM/YYYY' ;

or

Date(Date#(FromDate,'DD/MM/YYYY')) AS FromDate

[pokassov]

I didn't get your task first time...

Your script is ok for your task. You have status twice only if Customerno or Status looks like equal but contain different values...For example 'Purchase' and 'Purchase '

[#5]

Hi Sergey,

This is great, I just had to remove the Group By clause, other than that it seems to be working.

thank you