Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Experts,
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:
PREV_STATUS
): Derived from previous weeks' data.CURR_STATUS
): Derived from the current week's data.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 ;
Example Data:
Note : As mentioned, No data/row for 1/17/2024
SL No | Name | Date | Prev Week | This Week | Description |
1 | TEST - NAME - 1 | 6/07/2024 | - | 1 | |
2 | TEST - NAME - 1 | 6/14/2024 | 1 | 1 | No Change |
3 | TEST - NAME - 1 | 6/21/2024 | 1 | 1 | No Change |
4 | TEST - NAME - 1 | 6/28/2024 | 1 | 1 | No Change |
5 | TEST - NAME - 1 | 7/05/2024 | 1 | 1 | No Change |
6 | TEST - NAME - 1 | 7/12/2024 | 1 | 1 | No Change |
7 | TEST - NAME - 1 | 7/19/2024 | 1 | 1 | No Change |
8 | TEST - NAME - 1 | 7/26/2024 | 1 | 1 | No Change |
9 | TEST - NAME - 1 | 8/02/2024 | 1 | 1 | No Change |
10 | TEST - NAME - 1 | 8/23/2024 | 1 | 1 | No Change |
11 | TEST - NAME - 1 | 8/30/2024 | 1 | 1 | No Change |
12 | TEST - NAME - 1 | 9/06/2024 | 1 | 1 | No Change |
13 | TEST - NAME - 1 | 9/13/2024 | 1 | 1 | No Change |
14 | TEST - NAME - 1 | 9/20/2024 | 1 | 1 | No Change |
15 | TEST - NAME - 1 | 9/27/2024 | 1 | 1 | No Change |
16 | TEST - NAME - 1 | 10/04/2024 | 1 | 1 | No Change |
17 | TEST - NAME - 1 | 10/11/2024 | 1 | 1 | No Change |
18 | TEST - NAME - 1 | 10/18/2024 | 1 | 1 | No Change |
19 | TEST - NAME - 1 | 10/25/2024 | 1 | 1 | No Change |
20 | TEST - NAME - 1 | 11/01/2024 | 1 | 1 | No Change |
21 | TEST - NAME - 1 | 11/08/2024 | 1 | 1 | No Change |
22 | TEST - NAME - 1 | 11/15/2024 | 1 | 1 | No Change |
23 | TEST - NAME - 1 | 11/22/2024 | 1 | 1 | No Change |
24 | TEST - NAME - 1 | 11/29/2024 | 1 | 1 | No Change |
25 | TEST - NAME - 1 | 12/06/2024 | 1 | 1 | No Change |
26 | TEST - NAME - 1 | 12/13/2024 | 1 | 1 | No Change |
27 | TEST - NAME - 1 | 12/20/2024 | 1 | 1 | No Change |
28 | TEST - NAME - 1 | 12/27/2024 | 1 | 1 | No Change |
29 | TEST - NAME - 1 | 1/03/2025 | 1 | 1 | No Change |
30 | TEST - NAME - 1 | 1/10/2025 | 1 | 1 | No Change |
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;
Or something similar?
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;
Or something similar?