App tracking changes in the base list

[iczkla]

Hello,

I am looking to create an app which would allow me to track changes within base list. So with the first load I want to load a list of products that belong to specific product line which will be my baseline. Then on a monthly basis I would load there list of products from entire company and check it against my existing baseline to identify:

- if any details of the existing products have changed

- if some products were removed and which

- if products were added to this specific product line - if yes add to the baseline

I was wondering how should I go around my load to get desired results? Any help will be greatly appreciated!

[Andrei_Cusnir]

Hello,

I believe that this use case scenario is very specific and most probably very complicated to achieve in Qlik Sense, if it is even possible to do so 100%. Qlik Sense is mainly used to analyze data from a dataset, rather than prep and process the data. However, in your specific use case scenario, you have a lot of checks and you might need to use multiple functions to achieve, at least, some parts of it.

In case other community members might be able to help you achieve that by sharing with you a complete Data load editor script it will be great, but please allow me to share with you a general logic behind a successful implementation:  

You can make all the processes with a Python script and then use Qlik Sense to load the processed data and present it. The general idea would be to follow similar steps as below:

1. Create an EXCEL file that will have your baseline data there
2. Create an EXCEL file with all the company products that you want to load each month
3. Create a Python script that will allow you to read both files using pandas package and store them as dataframes
4. Then using those dataframes you can iterate through the data and make the necessary checks. For example:
   • Search all the products in the company's products list and see if it exists in the baseline list. Then in the baseline dataframe you can mention in a separate field the statement "REMOVED" in case the product was not found.
   • Then you can search each product detail from data with all products and compare with the details for the same product in the data baseline. After that you can create a new dataframe and there store all the products that were modified and also mention which fields were modified with boolean variable etc.
   • Finally you when you reach to a product that doesn't exists in the baseline dataframe, you can add it there and under new field mention "NEW" if you want, so that you know which products were added and which were removed.  
5. After all these checks you can store the new dataframes, that you have created, in an updated version of the baseline EXCEL document.
6. Make sure that the document always stays in the same location and then create an app in Qlik Sense that will read the document.
7. You can then always run the Python script for new files and as soon as it is ready, you can reload the Qlik Sense app as well.
8. This will allow you to analyze the data that is already processed and you can create visualizations to display how many products are new, how many products are deleted, which products are deleted, color the records with expression colors and much more. 

As you can see, the logic behind the process is fairly complicated and this is why I believe that it is going to take a lot of effort to achieve it within the Qlik Sense Data Load Editor script directly. Additionally, you can take a look at the Data Prep [1], where it is stated "Data prep involves taking raw data from multiple sources, cleaning it, validating it, structuring it and enriching it to prepare it for use in business analytics and business intelligence (BI) projects."

You can also reach to the proper team for support, by clicking "Contact us" and discuss the use case scenario that you have. I believe that you might be able to achieve most of your use case scenario if not 100% of it.

I hope that this information was helpful! 

---

[1] https://www.qlik.com/us/data-ingestion/data-prep