Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
See why Qlik was recognized for the seventh year in a row – and discover how we can help you tackle your data integration challenges. Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
GartoJsar
Partner - Contributor III
Partner - Contributor III

Compare updated data in a field

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

 

Labels (1)
1 Solution

Accepted Solutions
mahaveerbiraj
Creator II
Creator II

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.

View solution in original post

3 Replies
GartoJsar
Partner - Contributor III
Partner - Contributor III
Author

Hello
Is there anyone who can help me?

mahaveerbiraj
Creator II
Creator II

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
Partner - Contributor III
Partner - Contributor III
Author

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