Qlik Sense User Status changed in master table

[profilejamesbond]

Hi,

I have a user master table where I have user status and expiration date while I have user detail table where I have user activities such as last login data. Shown in the tables.

Now, the problem is I have only one master table field where the status change from active to notice to terminated etc. The user was active or notice period during that I have detail table data and there was no expiration date filled. Expire date was empty and now, the expire data filled and the user is terminated. The problem is now I see terminated status for all the months because there is no historization.

How to fix this thing or implement solution of logic for these months. I have one master table and one detail table that's it.

Thanks

[marcus_sommer]

You need to create your own historic data-table with a SCD logic, see:

Slowly Changing Dimensions - Qlik Community - 1464187

[profilejamesbond]

Hi @marcus_sommer,

I don't have end date. I have only end date when the user contract end otherwise it is changing the state which is no longer recorded in the system only the current status shown in the system.

Thanks

[marcus_sommer]

That's rather the common case and means that this kind of information must be separately extracted / generated by loading the current state and comparing them with the historic one and if there is a difference you have with today() the new start-date and with today() - 1 the previous end-date.

If this information doesn't native exists within the source it must be created. Before going this way you may investigate if these data are really not existing or if you could transfer the task to another team, for example your DWH.