Script duplicate data

[Rodriguez22]

Hi everyone,

Please your help with the following issue I have.  I have a customer table with many fields that can change on a given date, and our system creates a new row when a change happens, for example in the table below there was a change un the phone number on January 5 and later a change in salesman on January 10, both changes for the same customer.

Name	Last Name	City	Phone	Salesman	Date_of_change
Juan	Perez	Washington	456789	Walter	01/01/2022
Juan	Perez	Washington	123652	Walter	05/01/2022
Juan	Perez	Washington	123652	Michael	10/01/2022

What I need is the following table (assuming today is January 12), where we can "duplicate" the data between every date change. This complete table will be my new Customer Table.

Name	Last Name	City	Phone	Salesman	Date_of_change
Juan	Perez	Washington	456789	Walter	01/01/2022
Juan	Perez	Washington	456789	Walter	02/01/2022
Juan	Perez	Washington	456789	Walter	03/01/2022
Juan	Perez	Washington	456789	Walter	04/01/2022
Juan	Perez	Washington	123652	Walter	05/01/2022
Juan	Perez	Washington	123652	Walter	06/01/2022
Juan	Perez	Washington	123652	Walter	07/01/2022
Juan	Perez	Washington	123652	Walter	08/01/2022
Juan	Perez	Washington	123652	Walter	09/01/2022
Juan	Perez	Washington	123652	Michael	10/01/2022
Juan	Perez	Washington	123652	Michael	11/01/2022
Juan	Perez	Washington	123652	Michael	12/01/2022

[Bill_Britt]

Hi,

If the orginal data and the changed are not maintained in the Data source we will not see the orginal data

[MayilVahanan]

Hi

Try like below

Temp:
LOAD *, Name & [Last Name] as FullName INLINE [
Name, Last Name, City, Phone, Salesman, Date_of_change
Juan, Perez, Washington, 456789, Walter, 01/01/2022
Juan, Perez, Washington, 123652, Walter, 05/01/2022
Juan, Perez, Washington, 123652, Michael, 10/01/2022
];

Load *,Date(Date_of_change + IterNo()-1) as Dates While Date(Date_of_change + IterNo()-1) < NextDate;
Load *, Date(Alt(Previous(Date_of_change), MakeDate(2022,01,12))) as NextDate Resident Temp order by Date_of_change desc, FullName;

DROP Table Temp;

DROP Field FullName, NextDate, Date_of_change;
RENAME Field Dates to Date_of_change;

[MayilVahanan]

Instead of MakeDate(2022,01,12), change to Today() for realtime example.

Hope it helps you

[Rodriguez22]

Thank you very much @MayilVahanan , it works, but only when we have one customer, I tried to execute the script with two customers and the output is not what we want. For example:

The output shows me only the iteration for the first customer:

My database has a lot of customers with different dates changes.

[MayilVahanan · Accepted solution]

Hi

Try like below

Temp:
LOAD *, Name & [Last Name] as FullName INLINE [
Name, Last Name, City, Phone, Salesman, Date_of_change
Juan, Perez, Washington, 456789, Walter, 01/01/2022
Juan, Perez, Washington, 123652, Walter, 05/01/2022
Juan, Perez, Washington, 123652, Michael, 10/01/2022
Marco, Andarade, Houston, 694512, Michael, 01/01/2022
Marco, Andarade, Houston, 694512, Walter, 05/01/2022

];

Load *,Date(Date_of_change + IterNo()-1) as Dates While Date(Date_of_change + IterNo()-1) < NextDate;
Load *, If(FullName= Previous(FullName), Date(Alt(Previous(Date_of_change), MakeDate(2022,01,12))), MakeDate(2022,01,12)) as NextDate Resident Temp order by FullName, Date_of_change desc;

DROP Table Temp;

DROP Field Date_of_change;
RENAME Field Dates to Date_of_change;

Similar, change MakeDate(2022,01,12) to Today()

[Rodriguez22]

Dear @MayilVahanan , it works very well!!!!

Thanks a lot!!