Compare updated data in a field

[GartoJsar]

good morning
I need your support, I have a client table where they ask me to identify the personal data of the client that was modified. Then when you select the customer key and if the customer appears 2 times registered but with the modified data, place a message indicating that the name data was modified for example:

Customer_Key | Customer_Name | Address | Telephone | Start_Date | End date
01 | Juanito Perez | cd mex | 557890 | 02/01/2019 | 02/28/2019
01 | Juan Perez | cd mex | 7768584 | 02/28/2019 | 01/01/2040

In this example I have the client who registered Start Date 02/01/2019 and End Date 02/28/2019 indicating that on that date a modification of his data was made, in the second record already modified his Start Date is now 02/28/2019 .

Then in this example the name of the customer and phone number was modified. What I need to do is to make the comparison of each field where their records were modified and on a small table place the fields and whether you indicate me with a message, that field was updated.

I hope you can help me.
I thank you very much
Thank you

[GartoJsar]

Hello
Is there anyone who can help me?

[mahaveerbiraj · Accepted solution]

Hi Gartojsar,

please follow below steps it my help you.

ClientTemp:
load *
Inline
[
Customer_Key , Customer_Name , Address , Telephone , Start_Date , End_date
01 , Juanito Perez , cd mex , 557890 , 02/01/2019 , 02/28/2019
01 , Juan Perez , cd mex , 7768584 , 02/28/2019 , 01/01/2040
] ;

Client:
load *,
if(Customer_Name=Previous(Customer_Name),'No','Yes') as Customer_Name_Change,
if(Address=Previous(Address),'No','Yes') as Address_Change,
if(Telephone=Previous(Telephone),'No','Yes') as Telephone_Change,
if(Start_Date=Previous(Start_Date),'No','Yes') as Start_Date_Change,
if(End_date=Previous(End_date),'No','Yes') as End_date_Change

Resident ClientTemp Order by Customer_Key asc, Start_Date asc ;
Drop Table ClientTemp;

// please let me know if your not clear with steps.

[GartoJsar]

Muchas Gracias por tu respuesta Mahaveerbiraj:

Me ayudo de mucho, yo realice  un cambio a las condiciones, ya que si los dejaba como tu me lo pasaste, no funcionaba al 100%, esto debido a que en mis datos tengo clientes que estan registrados una vez y no han tenido modificaciones y aplicando tu condición me mostraba que tambien ese registro realizaba cambios, yo pienso que como estos clientes que no tienen mas de un registro al aplicar la regla yo creo que considera nulos los datos de estos clientes y por eso los considera como cambios todos sus campos. Para los clientes que si aparecen dos veces sus datos modificados si me realizaba correctamente bien cambio.

Entonces agregue esto en las condiciones, y con esto solo me indica cuales fueron modificados sin tomar en cuenta los clientes que solo estan registrados una vez.

les comparto mi condición por si algún dia tienen el mismo problemita que tuve.

if(FECHA_INICIO = Previous(FECHA_FIN) AND Trim(TELEFONO_NUMBER_INF02) <> Previous(trim(TELEFONO_NUMBER_INF02)),'Télefono',Null()) AS TEL_PP,
if(FECHA_INICIO = Previous(FECHA_FIN) AND Trim(EMAIL_ADDR_INF02) <> Previous(trim(EMAIL_ADDR_INF02)),'Email',Null()) AS EMAIL_PP,
if(FECHA_INICIO = Previous(FECHA_FIN) AND Trim(DIRECCION_STREET_INF02) <> Previous(trim(DIRECCION_STREET_INF02)),'Dirección',Null()) AS DIR_PP,
if(FECHA_INICIO = Previous(FECHA_FIN) AND trim(DIR_HOUSE_NUM1_INF02) <> Previous(trim(DIR_HOUSE_NUM1_INF02)),'Número',Null()) AS NUM_PP,
if(FECHA_INICIO = Previous(FECHA_FIN) AND trim(ZSTATUSCO_DES_INF02) <> Previous(trim(ZSTATUSCO_DES_INF02)),'Estatus',Null()) AS ESTATUS_PP,
if(FECHA_INICIO = Previous(FECHA_FIN) AND trim(NOMBRE_CLIENTE_INF02) <> Previous(trim(NOMBRE_CLIENTE_INF02)),'Nombre Cliente',Null()) AS NOMBRE_CLIENTE_PP