Splitting a record into multiple records

[Former Member]

Hi Experts,

We need to split a record into multiple records i.e. in multiple weeks based on the masterdata value.

This is because Event E1 starts from Week 36 to 41 and hence claim amount needs to be equally split into those weeks with extra cent going to the last week.

Is there some best modeling method to achieve this or we need to use SQL procedure to split a single record into multiple records ?

really appreciate your input on this topic.

Thanks,

Jomy

[michael_eaton3 · Accepted solution]

Hello

This is possible with various solutions.  Some people might think of using imperative constructs in a scripted calculation view or procedure to implement this, but as we're working with a relational database we can use (pseudo) relational theory to achieve it.

By simply rewriting your requirement from splitting a record into multiple records to multiplying 1 row by a number of weeks we give ourselves a clue on how to achieve this - multiplying datasets is easily done with a join.

Joining your original dataset to a dataset of weeks with some simple calculated columns can achieve your desired result.  Below is a simple example to demonstrate the theory.  I used a graphical calculation view as they're easier to maintain, and I constantly have hope that the optimiser can create the most performant execution plan.

Here's my test data.  Note the week table - this should contain sets of weeks up to the maximum required for your data.

create schema multiply_demo;

grant select on schema multiply_demo to "_SYS_REPO" with grant option;

set schema multiply_demo;

create column table source_claim (

  claim_id varchar(2),

  week_start int,

  week_end int,

  claim_amount int

);

create column table week_multiplier (

  week_set int,

  week_ord int

);

insert into source_claim (claim_id, week_start, week_end, claim_amount)

  values ('C1', 1, 1, 100);

insert into source_claim (claim_id, week_start, week_end, claim_amount)

  values ('C2', 2, 5, 1000);

insert into source_claim (claim_id, week_start, week_end, claim_amount)

  values ('C3', 1, 2, 300);

insert into source_claim (claim_id, week_start, week_end, claim_amount)

  values ('C4', 3, 5, 100);

insert into week_multiplier (week_set, week_ord) values (1, 1);

insert into week_multiplier (week_set, week_ord) values (2, 1);

insert into week_multiplier (week_set, week_ord) values (2, 2);

insert into week_multiplier (week_set, week_ord) values (3, 1);

insert into week_multiplier (week_set, week_ord) values (3, 2);

insert into week_multiplier (week_set, week_ord) values (3, 3);

insert into week_multiplier (week_set, week_ord) values (4, 1);

insert into week_multiplier (week_set, week_ord) values (4, 2);

insert into week_multiplier (week_set, week_ord) values (4, 3);

insert into week_multiplier (week_set, week_ord) values (4, 4);

insert into week_multiplier (week_set, week_ord) values (5, 1);

insert into week_multiplier (week_set, week_ord) values (5, 2);

insert into week_multiplier (week_set, week_ord) values (5, 3);

insert into week_multiplier (week_set, week_ord) values (5, 4);

insert into week_multiplier (week_set, week_ord) values (5, 5);

Here is the structure of the calculation view - a projection and a join.

I have a calculated column in proj_Claim to calculate the total number of weeks

The join is on the calculated column - perhaps not the best idea for performance, but the optimiser can deal with that.

The final projection has a calculated column to allocate the claim to each of the weeks.  If you require logic do to do something different in your final week, simply modify this expression.  The final week can be determined when week_count <> 1 and week_ord = week_count.

Here is the result of querying this model, I'm showing all columns only for demonstration.

Michael

[lbreddemann · Accepted solution]

Classic. Oldie but goodie!

[Former Member · Accepted solution]

Hi Micheal,

First of all thanks for a wonderful illustration.One question: I'm not very clear on the Week multiplier. what is the relevance of the two attributes of that table and how does it help?

Also If you see my claim data source example, it just has week for which the claim is for.

Now its is the Event masterdata which says if the event is from which week to week.

So first we need to do a union on Event Masterdata and Claim transacation data to get to start week and end week.

Thanks,

Jomy

[michael_eaton3 · Accepted solution]

Hello

If you build the example you'll see the relevance of the 2 columns in the week_multiplier table; week_set is used in the join condition and week_ord is an ordinal number, which becomes useful in the calculations.

In your situation, you will have to assemble the relevant data before allocating across the weeks, but as you omitted detail of the event master data, I can't guess what the structure is.  I would expect a join to be used there too though.

Michael

[michael_eaton3 · Accepted solution]

Yep, I roll it out once a year just to show I can still do it!