Best way to compare 2 lists, todays data vs the same data from 10 days ago?

[OdieW]

I am using the qlik entitlement analyzer app along with active directory data in an automation to manage qlik users. For one part of the automation I want to compare 2 lists to see what users existed in that list 7-10(probably 10) days ago and still exist in the list today. I have tested this in my automation by exporting data from the app to an excel file and using that excel file to load data into a new app that I don't refresh. Everything works fine, but I want a more automated/better solution long term. I know I can make a copy of the entitlement analyzer app and only have it refresh every 10 days, but then I have to manage both those apps when there are updates. We have made some changes to the entitlement analyzer app so I'd have to recreate those changes in both apps and I'd prefer not to have to do that.

I considered trying to automate making a copy of the entitlement analyzer QVD's, but I don't think the automation can access QVD data without it being loaded into an app so I would still need 2 apps.

Is there a better option I haven't considered? Maybe some way to have the app/QVD refresh daily but have 1 table in the app that only updates every 10 days? In my research I have also seen mention of adding another field like " load date" that I assume I would only want to have that field update every 10 days then load data using that field as a filter to show the state of the data from 10 days ago, but I'm not sure I fully understand that. It's also possible I am overthinking this and there is an easier way to do this. Currently I am researching if I can use the snapshot feature to populate a table every 10 days. Any other ideas? Thanks!

[marksouzacosta · Accepted solution]

Hi @OdieW,

You can do this in many different ways, not sure which one will be the easiest:

1 - You could collect the data straight from Qlik REST APIs and store it in a QVD file (if you know exactly what you are looking for)

2 - [Probably easier] You can binary load the Entitlement Analyzer App in a separated Qlik Sense Application to extract the data you are looking for as QVD files. Then you can control when this application is reloaded to collect and store the records you are looking for.

In both cases, you can create a field called SnapshotDate to inform when the record was stored.

Regards,

Mark Costa

[OdieW]

Thanks for the quick response @marksouzacosta 

1 - You could collect the data straight from Qlik REST APIs and store it in a QVD file (if you know exactly what you are looking for). The entitlement analyzer app is using the rest API to collect the data now. I don't really want to modify the load script too much as it will revert any time we upgrade the app. So I assume to use the Rest APIs to get the data I would need to use a 2nd app? I suppose I could do that just for the data I need. Was trying to avoid making a copy of the entitlement analyzer app or even using a 2nd app at all just because its more to manage, but will if I have to.

I'd actually love to just use the rest API data for this automation, and I attempted it, but this same compare is troublesome when using just API data as I need to get a distinct row for each user based on the Max of EndDate, and I'm sure its possible to do but my knowledge/use of APIs is limited. Really enjoying learning who to use them though.

2 - [Probably easier] You can binary load the Entitlement Analyzer App in a separated Qlik Sense Application to extract the data you are looking for as QVD files. Then you can control when this application is reloaded to collect and store the records you are looking for. I did briefly look into binary load. Don't recall the reason but I feel like I abandoned it for some reason, perhaps incorrectly. This would again require a second app though. The binary load just loads the data from the app, it doesn't process the storing of QVD's like the original app load script correct? I just don't want to mess with the entitlement analyzer QVD's even though it wouldn't really hurt anything.

In both cases, you can create a field called SnapshotDate to inform when the record was stored.

I am testing using a snapshot date currently. Still a little confused on how it works exactly. That field won't work in the app/visual alone right, cause the data would still get overwritten with refreshed data assuming I use it in the app that refreshes daily. If I use a second app that only refreshes every 10 days I'm not sure I'd even need the field. Although I can see its usefulness in ensure the reload date of the data.

Update: I think I am liking the Binary load option if I am understanding it correctly. It does require a second app, but it won't really require making the same changes needed to the entitlement analyzer app like if I had just used a copy of that app. I am having one issue with storing a QVD. In the original app I am just using a table visual with like 5 or 6 fields from the load script as it is. When I try to build a table in the load script with the fields I need it says it can't find the table for the store qvd, even though when I used the insert store statement wizard thing, it sees the table there.