Duplicate a row and change values

[#1]

Hi guys,

I have this table that is in my database,

Claim #	Work Order #	Arrival Date	Service Complete Date
145	251295	11/24/2014	01/21/2015
146	251296	03/22/2015	5/10/2015
147	251297	01/30/2015	02/30/2015

I need broke the months in rows like this, each row will be a month

Claim #	Work Order #	Arrival Date	Service Complete Date
145	251295	11/24/2014	11/30/2015
145	251295	12/01/14	12/31/2015
145	251295	01/01/14	01/21/2015
146	251296	03/22/2015	03/31/2015
146	251296	04/01/2015	04/30/2015
146	251296	05/01/2015	05/10/2015
147	251297	01/30/2015	01/31/2015
147	251297	02/01/2015	02/30/2015

My question is, how can I do that in my script?

Directory;

LOAD [Claim #],

     [Work Order #],

     [Arrival Date],

     [Service Complete Date]

FROM

Test.xlsx

(ooxml, embedded labels, table is Plan1);

Thanks.

[anbu1984 · Accepted solution]

SET MonthDiff = Num(((year($2) * 12) + month($2)) - (((year($1) * 12) + month($1))) + 1);
SET DateFormat='MM/DD/YYYY';


Load Claim,[Work Order],If(IterNo()=1,[Arrival Date],
Date(MonthStart(AddMonths([Arrival Date],IterNo()-1)))) As [Arrival Date],
If(IterNo()=IterNo,[Service Complete Date],
Date(MonthEnd(AddMonths([Arrival Date],IterNo()-1)))) As [Service Complete Date]
While IterNo() <= IterNo ;
Load *,$(MonthDiff([Arrival Date],[Service Complete Date])) As IterNo;
Load Claim,[Work Order],Date#([Arrival Date],'MM/DD/YYYY') As [Arrival Date],Date#([Service Complete Date],'MM/DD/YYYY') As [Service Complete Date] Inline [
Claim,Work Order,Arrival Date,Service Complete Date
145,251295,11/24/2014,01/21/2015
146,251296,03/22/2015,5/10/2015
147,251297,01/30/2015,02/28/2015 ];

[sujeetsingh]

What is your rule to repeat this rows

[#4]

Thanks for the reply Anbu,

How can I use this dynamically and load the data from my database?

LOAD [Claim #],

     [Work Order #],

     [Arrival Date],

     [Service Complete Date]

FROM

Test.xlsx

(ooxml, embedded labels, table is Plan1);

[anbu1984]

Replace Inline load by Excel

Replace this

Load Claim,[Work Order],Date#([Arrival Date],'MM/DD/YYYY') As [Arrival Date],Date#([Service Complete Date],'MM/DD/YYYY') As [Service Complete Date] Inline [  
Claim,Work Order,Arrival Date,Service Complete Date  
145,251295,11/24/2014,01/21/2015  
146,251296,03/22/2015,5/10/2015  
147,251297,01/30/2015,02/28/2015 ];

By

Load Claim,[Work Order],Date#([Arrival Date],'MM/DD/YYYY') As [Arrival Date],Date#([Service Complete Date],'MM/DD/YYYY') As [Service Complete Date]
FROM
Test.xlsx
(ooxml, embedded labels, table is Plan1);

[#6]

Excelent Anbu,

Works Perfectly!