Splitting records based on effective date

vinodnr · Post by **vinodnr** » Thu Oct 23, 2014 10:16 pm

Need help with logic to achieve below output:

Below are the source records:

KEY1|N|20140101|99991231
KEY1|Y|20140901|20200101

These needs to be split as below:

KEY1|N|20140101|20140901
KEY1|Y|20140901|20200101
KEY1|N|20200101|99991231

Regards

chulett · Post by **chulett** » Fri Oct 24, 2014 6:23 am

Spell out your requirements in words. This not only helps us but often times you as well.

vinodnr · Post by **vinodnr** » Fri Oct 24, 2014 9:23 am

We have Non active records and Active records overlapping. We need to adjust the date for Non active records so that they will not spill into the active records.
In the below example, for the Key1, we have non active record starting at 20140101 and ending at 99991231 and for the same key, we have active record starting from
20140901 to 20200101.

Now we need to adjust dates as 20140101 to 20140901 non active record, 20140901 to 20200101 active record as it is and 20200101 to 99991231 non active record for the remaining time

Current Records:
KEY1|N|20140101|99991231
KEY1|Y|20140901|20200101

Expected Records:
KEY1|N|20140101|20140901
KEY1|Y|20140901|20200101
KEY1|N|20200101|99991231

Regards,
Vinod N

ray.wurlod · Post by **ray.wurlod** » Fri Oct 24, 2014 3:38 pm

So, if I read this right, the active record is passed through unchanged, and the inactive record is split into two, with the end date of one being the start date of the active record, and the start date of the other being the end date of the active record. Is this the correct reading?

If so, sort by key then by active flag descending. Use stage variable to "remember" the active record then build the two active records on two separate output links using the dates from the stage variables. Downstream of the Transformer stage use a Funnel stage to bring all records back together.

Maybe you can call the output links from the Transformer stage Inactive_Before, Active and Inactive_After.

vinodnr · Post by **vinodnr** » Sat Oct 25, 2014 5:57 pm

Hi Ray,

Thanks for the reply. The assumption you made is absolutely correct. I came across another scenario where there would be multiple active records and Inactive records. We may need to split inactive records into 3 or ever into 4. Below is the example.
Active Records:
------------------
KEY1|Y|20140101|20150101
KEY1|Y|20160101|20170101

Inactive record:
--------------------
KEY1|N|20130501|20180101

Expected output should be:
-------------------------------
KEY1|N|20130101|20140101
KEY1|Y|20140101|20150101
KEY1|N|20150101|20160101
KEY1|Y|20160101|20170101
KEY1|N|20170101|20180101

Regards,
Vinod

ray.wurlod · Post by **ray.wurlod** » Sat Oct 25, 2014 10:06 pm

You should be able to generalize what I wrote earlier. You may need to loop through all the records for a particular key, sorted in flag order (descending) and date order (ascending). Other than that, the logic will be very similar. Why not attempt it?