I am working on a Qlik script to track configuration status changes for applications over time, including identifying when an application is "Archived." My data comes from weekly snapshots stored in a QVD file.
In my script:
Historical Status (PREV_STATUS): Derived from previous weeks' data.
Current Status (CURR_STATUS): Derived from the current week's data.
I am using the Previous() function to identify status changes between the current and previous weeks.
Issue: The "Application is Archived" flag is supposed to mark applications that existed in the previous week (PREV_STATUS is not null) but are missing in the current week's data (CURR_STATUS is null). However, the issue arises because there is no data for the current week for these archived applications, making it impossible to directly compare current and previous statuses.
what changes do I have to make to the script to achieve the desired outcome?
Thank you for your help.
Qlik Script:
FACT: Load * From MAIN.QVD;
STATUS_CHANGE: Load *, If (THIS_WEEK_STATUS= [Previous Status], 'No', 'Yes') as [Status Change Flag] ,
If (THIS_WEEK_STATUS = '1' and [Previous Status] = '0', 'From Non-Active to Active', If (THIS_WEEK_STATUS = '0' and [Previous Status] = '1', 'From Active to Non-Active', If (THIS_WEEK_STATUS = '1' and isnull( [Previous Status]) or Len([Previous Status]) = '0' ,'New', If (THIS_WEEK_STATUS= [Previous Status], 'No Change in Status', 'Other') ) ) ) as [Status Change DESC] ;
LOAD NAME, Date , STATUS as THIS_WEEK_STATUS, //Curr Week Status If( NAME=Previous(NAME), Previous(STATUS)) as [Previous Status] //PREV WEEK STATUS RESIDENT FACT ORDER by NAME, Date ;
Do I understand you correctly that an app that gets archived on week 30 doesn't have a row for week 31 and hence you can't mark week 31 as archived?
In this case you would have to generate a line to the data. If you only want week 31 to be added, but not week 32 and so on, you could find the max date and add a week. You would probably also need to handle the current week as you don't want all the apps to be currently flagged archived.
// Add the max date so that we later can find if the app is active today left join (FACT) Load Name, max(Date) as MaxDate resident FACT group by Name;
concatenate (FACT) Load max([SL No])+1 as [SL No], Name, date(max(Date)+7) as Date, 1 as [Prev Week], 0 as [This Week] resident FACT where MaxDate < today()-7 // So that we don't add a snapshot for the current week group by Name;
Do I understand you correctly that an app that gets archived on week 30 doesn't have a row for week 31 and hence you can't mark week 31 as archived?
In this case you would have to generate a line to the data. If you only want week 31 to be added, but not week 32 and so on, you could find the max date and add a week. You would probably also need to handle the current week as you don't want all the apps to be currently flagged archived.
// Add the max date so that we later can find if the app is active today left join (FACT) Load Name, max(Date) as MaxDate resident FACT group by Name;
concatenate (FACT) Load max([SL No])+1 as [SL No], Name, date(max(Date)+7) as Date, 1 as [Prev Week], 0 as [This Week] resident FACT where MaxDate < today()-7 // So that we don't add a snapshot for the current week group by Name;
