How to bifurcate data on the basis of Date

[pra_kale]

Hi,

I have created one sample data module which consist of 4 tables as given below.

1) Premium

2) Overall...Fact Table

3) Issuance

4) Product_UIN

In product_UIN table i have product codes and UIN and their launch dates. In summary table i want to bifurcate data on the basis of product launch date. For e.g. In case of Product code FCD i want data on or before  13-09-2015 should reflect under UINNo ACDVI1 and after 13-09-2015 should reflect under UINNo ACDVI2.

Final out-put is given below..

Product_Code	UIN	Policy_Count	Premium Total
ABC	ABVIP1	3	82000
ABC	ABVIP2	4	122000
DEF	DEXDI2	1	8000
FCD	ACDVI1	3	93000
FCD	ACDVI2	2	37000
JKL	JKLVD2	1	3000
UAX	UAXDI1	3	577000
UAX	UAXDI2	1	195000
UIN	ABCDV1	2	39000
Grand Total		20	1156000

Please guide how i will get the final out-put.

Thanks in advance.

[Gysbert_Wassenaar]

Your document contains these records for FCD:

ProductCode	UINNo	launchDate
FCD	ACDVI1	13-09-2015
FCD	ACDVI2	14-09-2015

It makes no sense to me if you want for ACDVI1 the policy numbers that lie before the launchDate and for ACDVI2 those on or after the launchDate. 13-09-2015 is obviously not a launch date. Suppose your table contains these records:

ProductCode	UINNo	launchDate
FCD	ACDVI1	13-09-2015
FCD	ACDVI2	14-09-2015
FCD	ACDVI3	01-01-2016
FCD	ACDVI4	02-01-2016

Now which policies belong to which UINNo?

I think you should first create a clean Product_UIN table that contains the real launch dates. Otherwise it's next to impossible to correctly 'bifurcate data'

[pra_kale]

Hi,

I agree with you Gysbert. Column name which i have given in the file is confusing because i have kept launch date for both the UIN.

Now i have change the column name and kept launchDate/EndDate.

Actually the thing is, dates mentioned in front of product codes FCD and UINNo ACDVI1 , UAX and UAXDI1, ABC and ABVIP1 are UIN End Dates where as product codes FCD and UINNo ACDVI2 , UAX and UAXDI2, ABC and ABVIP2 are having UIN Starts Dates. It means the policies issued on or before 13-09-2015 in case of FCD products should bifurcated under UIN ACDVI1 and policies issued after 14-09-2015 should carry UIN ACDVI2. same thing hold goods for other products as well.

[Gysbert_Wassenaar]

That's great.

Actually just renaming the field doesn't help at all. The data sucks regardless of the name of the field. It takes a human to figure out what should be done. A computer can't make such guesses correctly. What your table should look like is something like this:

ProductCode	UINNo	launchDate	EndDate
UIN	ABCDV1	1/1/1900
FCD	ACDVI1	1/1/1900	9/14/2015
FCD	ACDVI2	9/14/2015
UAX	UAXDI1	1/1/1900	10/13/2015
UAX	UAXDI2	10/13/2015
DEF	DEXDI2	1/1/1900
GHI	GHIJD1	1/1/1900
JKL	JKLVD2	1/1/1900
ABC	ABVIP1	1/1/1900	5/6/2015
ABC	ABVIP2	5/6/2015

Then you can calculate intervals in which the UINNo's are valid and match the dates of the policies with the intervals using the IntervalMatch function.

[pra_kale]

Hi,

Thank you very much Gysbert. You are really great..!!!

What you have mentioned is absolutely true. I have to modify the product file the way you did.

As I  so far never used Intervalmatch function. Can you please help how i should use in above case. As my Issuance data and Premium data is coming from 2 different files where as my main file contains Product and their dates.

Can you please help how is to be done..

[Gysbert_Wassenaar]

First read this document: IntervalMatch and Slowly Changing Dimensions

Note, you have intervals for each ProductCode. That means you need to use ProductCode as key in the intervalmatch function so the issuedates will be matched with the intervals that have the same ProductCode as the policy has:

IntervalMatchTable:

IntervalMatch(IssueDate, ProductCode)

LOAD

     launchDate, EndDate ProductCode

RESIDENT

     ProductVersion;

There's one more thing you need to take into account. If a record doesn't have an EndDate value then you need to create one first. Otherwise the intervalmatch function doesn't find an interval. You can replace a missing EndDate with for example today's date: Alt(EndDate, Today()) as EndDate

[pra_kale]

Hi,

Thanks for your suggestion...

Just for simplification i have given start and end dates for every products. Below given is the code

but i think i have missed something important thing because below given code generating synthetic key. I have tried it by one another way by drooping  Product_UIN at the end but by doing this I am not getting UINNo.

Can you please help in correcting below given code..

Issuance:

LOAD Policy_no,

    Policy_no as Policy_Issuance

//    Year,

//    Month,

//    Channel,

//    Date

FROM

(ooxml, embedded labels, table is Issuance);

Overall:

LOAD Policy_no,

    Policy_no as Policy_Premium,

    Year,

    Month,

    Channel,

    Issuance_Date,

    ProductCode

FROM

(ooxml, embedded labels, table is Sheet1);

Product_UIN:

LOAD ProductCode,

    UINNo,

    Start_DT,

    END_DT

FROM

(ooxml, embedded labels, table is Sheet1);

Premium:

LOAD Policy_no as Policy_Premium,

//    Year,

//    Month,

//    Channel,

    Premium

FROM

(ooxml, embedded labels, table is Sheet1);

IntervalMatchTable:

IntervalMatch(Issuance_Date, ProductCode)

LOAD

      Start_DT, END_DT,ProductCode

RESIDENT

    Product_UIN;

[Gysbert_Wassenaar · Accepted solution]

Ok, try joining the intervalmatch table with the Overall table.

LEFT JOIN (Overall)

IntervalMatch(Issuance_Date, ProductCode)

LOAD

      Start_DT, END_DT,ProductCode

RESIDENT

     Product_UIN;

You'll still have one synthetic key left after that, but that's not a problem. If you want to get rid of that one as well you can next left join the Product_UIN table to the Overall table and then drop the Product_UIN table.

[pra_kale]

You are the Best Gysbert...

You have solve the problem in a fraction of second..

Thank you very much..

In real scenario i have more complex data model than what i have created here for testing purpose. But if i am not wrong the logic will still remain the same of interalMatch function and afterwords doing left join with main table which have Issuance dates.

Thanks again for your guidance.