Split dates from and to

[udaya_kumar]

Hi,

I have two dates in the data model

consider

ID,start_date,end_date,Value

1,01/02/2016,31/12/9999,10

2,01/03/2016,31/12/9999,25

3,01/01/2016,31/01/2016,5

4,01/02/2016,30/04/2016,30

I want to split the start date and end date as

if you consider ID 1, i want the output date as

ID,start_date,end_date,Value

1,01/02/2016,29/02/2016,10

1,01/03/2016,31/03/2016,10

1,01/04/2016,30/04/2016,10

1,01/05/2016,31/12/9999,10

Data is available till May Month(01/05/2016), So the end date for may month should come as 31/12/9999 to indicate it is the current data. Similarly it should split dates for all the ID's.

How can we achieve this split dates?

[sunny_talwar]

Try this:

Table:

LOAD Date(AddMonths(start_date, IterNo() - 1)) as start_date,

  Date(If(MonthEnd(AddMonths(start_date, IterNo() - 1)) > MakeDate(2016,5,1), end_date, MonthEnd(AddMonths(start_date, IterNo() - 1)))) as End_date,

  ID,

  Value

While AddMonths(start_date, IterNo() - 1) <= MakeDate(2016,5,1);

LOAD * Inline [

ID,start_date,end_date,Value

1,01/02/2016,31/12/9999,10

2,01/03/2016,31/12/9999,25

3,01/01/2016,31/01/2016,5

4,01/02/2016,30/04/2016,30

];

[udaya_kumar]

Hi Sunny,

Thank you for the reply.

The data given is just a sample from the data model.

The latest month data available is for may 2016, so i think we cannot use MakeDate(2016,5,1);

There is a row, previous to the last row in the above table snapshot, start date 01/05/2016 but the end date showing as 30/04/2016, i feel it is not right. Can you please check that?

[sunny_talwar]

How else can be determine the May Date? Max from database?

May be this to address the second issue

Table:

LOAD Date(AddMonths(start_date, IterNo() - 1)) as start_date,

  Date(If(MonthEnd(AddMonths(start_date, IterNo() - 1)) > MakeDate(2016,5,1) and

  MonthEnd(AddMonths(start_date, IterNo() - 1)) < end_date, end_date, MonthEnd(AddMonths(start_date, IterNo() - 1)))) as End_date,

  ID,

  Value

While AddMonths(start_date, IterNo() - 1) <= MakeDate(2016,5,1);

LOAD * Inline [

ID,start_date,end_date,Value

1,01/02/2016,31/12/9999,10

2,01/03/2016,31/12/9999,25

3,01/01/2016,31/01/2016,5

4,01/02/2016,30/04/2016,30

];

[udaya_kumar]

Yes, we can get max(start_date) then we can get it as May 2016.

I don't want the end date of may to show as 31/05/2016, it should be 31/12/9999, because that is the live date and before may month, we consider it as history data, 31/12/9999 identifies it as live data.

[sunny_talwar · Accepted solution]

This?

Table:

LOAD Date(AddMonths(start_date, IterNo() - 1)) as start_date,

  Date(If(MonthEnd(AddMonths(start_date, IterNo() - 1)) > MakeDate(2016,5,1), '31/12/9999', MonthEnd(AddMonths(start_date, IterNo() - 1)))) as End_date,

  ID,

  Value

While AddMonths(start_date, IterNo() - 1) <= MakeDate(2016,5,1);

LOAD * Inline [

ID,start_date,end_date,Value

1,01/02/2016,31/12/9999,10

2,01/03/2016,31/12/9999,25

3,01/01/2016,31/01/2016,5

4,01/02/2016,30/04/2016,30

];

[sasiparupudi1]

t4:

Load ID,Date(Date#(start_date,'DD/MM/YYYY')) as start_date,Date(Date#(end_date,'DD/MM/YYYY')) as end_date,Value;

LOAD * Inline

[

ID,start_date,end_date,Value

1,01/02/2016,31/12/9999,10

2,01/03/2016,31/12/9999,25

3,01/01/2016,31/01/2016,5

4,01/02/2016,30/04/2016,30

];

MaxEndDate:

Load Max(end_date) as MaxEndDate

Resident t4

where year(end_date)<>9999;

let vMaxEdnDate=Peek('MaxEndDate',0,'MaxEndDate');

NoConcatenate

t5:

Load ID,Date(AddMonths(start_date, IterNo() - 1)) as start_date,

       Date(If(MonthEnd(AddMonths(start_date, IterNo() - 1)) > Date(AddMonths($(vMaxEdnDate),1)) , end_date,MonthEnd(AddMonths(start_date, IterNo() - 1)))) as end_date ,

       Value

resident t4

while   AddMonths(start_date, IterNo() - 1) <= Date(AddMonths($(vMaxEdnDate),1)) ;

DROP Table t4;