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

bennywoo — Wed, 17 Aug 2016 04:31:04 GMT

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;

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

sunny_talwar — Thu, 08 Sep 2016 09:51:02 GMT

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

Best,

Sunny

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

bennywoo — Thu, 08 Sep 2016 10:36:05 GMT

Just for sharing.

Regards,

Benny

topic Re: Convert date in sequential to date range for using the IntervalMatch function in QlikView

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

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

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