After reading the HIC's post Creating a Date Interval from a Single Date
I have modified the script to handling records with status change.
Source Table
| date | vendor_no | status |
|---|
| 20160101 | Vendor1 | new |
| 20160102 | Vendor1 | new |
| 20160103 | Vendor1 | active |
| 20160104 | Vendor1 | active |
| 20160105 | Vendor1 | active |
| 20160106 | Vendor1 | active |
| 20160107 | Vendor1 | Inactive |
| 20160108 | Vendor1 | Inactive |
| 20160109 | Vendor1 | Inactive |
| 20160110 | Vendor1 | active |
| 20160111 | Vendor1 | active |
| 20160112 | Vendor1 | active |
| 20160101 | Vendor2 | active |
| 20160102 | Vendor2 | active |
| 20160103 | Vendor2 | active |
| 20160104 | Vendor2 | inactive |
| 20160105 | Vendor2 | inactive |
| 20160106 | Vendor2 | inactive |
| 20160107 | Vendor2 | active |
| 20160108 | Vendor2 | active |
| 20160109 | Vendor2 | deleted |
Target Table
Date range for the status change of each vendor_no
| vendor_from_date | vendor_to_date | vendor_no | status |
|---|
| 20160101 | 20160102 | Vendor1 | new |
| 20160103 | 20160106 | Vendor1 | active |
| 20160107 | 20160109 | Vendor1 | inactive |
| 20160111 | TODAY() | Vendor1 | active |
| 20160101 | 20160103 | Vendor2 | active |
| 20160104 | 20160106 | Vendor2 | inactive |
| 20160107 | 20160108 | Vendor2 | active |
| 20160109 | TODAY() | Vendor2 | deleted |
[vendor]:
LOAD date
, vendor_no
, status
, vendor_no & '|' & status as vendor_key
INLINE [
date, vendor_no, status
20160101, vendor1, new
20160102, vendor1, new
20160103, vendor1, active
20160104, vendor1, active
20160105, vendor1, active
20160106, vendor1, active
20160107, vendor1, inactive
20160108, vendor1, inactive
20160109, vendor1, inactive
20160110, vendor1, active
20160111, vendor1, active
20160112, vendor1, active
20160101, vendor2, active
20160102, vendor2, active
20160103, vendor2, active
20160104, vendor2, inactive
20160105, vendor2, inactive
20160106, vendor2, inactive
20160107, vendor2, active
20160108, vendor2, active
20160109, vendor2, deleted
];
//
// if the vendor status change, get the "date" to the new field vendor_from_date
// Remember to use the order by vendor_no, date.
//
inner join(vendor)
load
date
, vendor_no
, vendor_key
, If(vendor_key<>Peek(vendor_key),date#(date,'YYYYMMDD'),null()) as vendor_from_date
resident [vendor]
order by vendor_no,date;
//
// Only the vendor_from_date is not null records are needed, then set the vendor_to_date to the previous record's vendor_from_date - 1.
// Remember to use the order by vendor_no, vendor_from_date desc.
//
inner join(vendor)
load
date
, vendor_key
, vendor_no
, vendor_from_date
, If(vendor_no=Peek(vendor_no),date#(peek(date),'YYYYMMDD')-1,today()) as vendor_to_date
resident [vendor]
where vendor_from_date <> null()
order by vendor_no, vendor_from_date desc ;
drop field date;
drop field vendor_key;
