Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
bennywoo
Contributor II
Contributor II

Convert date in sequential to date range for using the IntervalMatch function

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

datevendor_nostatus
20160101Vendor1new
20160102Vendor1new
20160103Vendor1active
20160104Vendor1active
20160105Vendor1active
20160106Vendor1active
20160107Vendor1Inactive
20160108Vendor1Inactive
20160109Vendor1Inactive
20160110Vendor1active
20160111Vendor1active
20160112Vendor1active
20160101Vendor2active
20160102Vendor2active
20160103Vendor2active
20160104Vendor2inactive
20160105Vendor2inactive
20160106Vendor2inactive
20160107Vendor2active
20160108Vendor2active
20160109Vendor2deleted

Target Table

Date range for the status change of each vendor_no

vendor_from_datevendor_to_datevendor_nostatus
2016010120160102Vendor1new
2016010320160106Vendor1active
2016010720160109Vendor1inactive
20160111TODAY()Vendor1active
2016010120160103Vendor2active
2016010420160106Vendor2inactive
2016010720160108Vendor2active
20160109TODAY()Vendor2deleted

[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;

2 Replies
sunny_talwar

Is there a question here for us or did you just want to share what you were able to accomplish?

Best,

Sunny

bennywoo
Contributor II
Contributor II
Author

Just for sharing.

Regards,

Benny