Issue with "App is Archived" Flag

moh2_sid — Tue, 21 Jan 2025 21:24:07 GMT

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:

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 ;

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

@swuehl @killiandent

Re: Issue with "App is Archived" Flag

morgankejerhag — Wed, 12 Feb 2025 05:34:12 GMT

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?

topic Issue with "App is Archived" Flag in App Development

Issue with "App is Archived" Flag

Re: Issue with "App is Archived" Flag