Compare Previous day data to current day and display records which are missing for current day

[rajasekhar6067]

Hi, We have a requirement to compare data of previous day with current day and display the records that are
1) missed for the current day when compared to previous day and
2) if there is any change in Type column.

Date	Country	City	Type	Total
11/15/2019	US	NewYork	Open	30
11/15/2019	US	Washington	Open	45
11/15/2019	US	Dallas	Open	100
11/15/2019	IND	Mumbai	Open	75
11/15/2019	IND	Hyderabad	Open	63
11/16/2019	US	NewYork	Open	32
11/16/2019	US	Washington	Open	45
11/16/2019	IND	Mumbai	Open	75
11/16/2019	IND	Delhi	Open	99
11/17/2019	US	San Jose	Open	55
11/17/2019	US	NewYork	Open	32
11/17/2019	US	Washington	Close	45
11/17/2019	IND	Delhi	Open	99

When user select Data - 11/17, below should be the output

Country	City	Changed Attribute	Previous day value	Current day value
IND	Mumbai	Total	75
US	Washington	Type	Open	Close

Reason: When compared with 11/16, City - Mumbai is missing for date 11/17 and the Type for Washington is changed from Open to Close.

When user select Data - 11/16, below should be the output

Country	City	Changed Attribute	Previous day value	Current day value
IND	Hyderabad	Total	63
US	Dallas	Total	100

Reason: When compared with 11/15, Cities - Hyderabad and Dallas are missing for date 11/16.

We use Qlik sense Feb 2019 version.
Any leads would be much appreciated. Thanks in Advance.

[vunguyenq89 · Accepted solution]

Hi,

One way to solve this is with the following steps:

• Loop through each  unique Date in your data (except for the first date).
• At each iteration:
  1. Extract data of the current date and previous date
  2. Compare the two datasets and generate the values you need (Changed Attribute, Previous day value, Current day value)

Sample reload script:

Data:
LOAD Date(Date#(Date,'MM/DD/YYYY')) as Date,Country,City,Type,Total;
LOAD * INLINE [
Date	Country	City	Type	Total
11/15/2019	US	NewYork	Open	30
11/15/2019	US	Washington	Open	45
11/15/2019	US	Dallas	Open	100
11/15/2019	IND	Mumbai	Open	75
11/15/2019	IND	Hyderabad	Open	63
11/16/2019	US	NewYork	Open	32
11/16/2019	US	Washington	Open	45
11/16/2019	IND	Mumbai	Open	75
11/16/2019	IND	Delhi	Open	99
11/17/2019	US	San Jose	Open	55
11/17/2019	US	NewYork	Open	32
11/17/2019	US	Washington	Close	45
11/17/2019	IND	Delhi	Open	99
] (txt, delimiter is '\t');

UniqueDates:
LOAD Distinct(Date) Resident Data Order By Date;

DateCompare:
LOAD * INLINE [Date, Compare Country, Compare City, Changed Attribute, Previous day value, Current day value];

LET DateCount=NoOfRows('UniqueDates')-1;
For i=1 to $(DateCount) //Loop through each unique day
	// Get data for current day and previous day
	vCurrentDate=Peek('Date', $(i), 'UniqueDates');
    vPreviousDate = Date(vCurrentDate-1);
    PreviousDateData:
    NoConcatenate LOAD Date as PreviousDate, Country, City, Total as PreviousTotal, Type as PreviousType Resident Data WHERE Date = '$(vPreviousDate)';
    Outer Join(PreviousDateData)
    CurrentDateData:
    LOAD Date as CurrentDate, Country, City, Type as CurrentType Resident Data WHERE Date = '$(vCurrentDate)';
    
    // 1. Find missing entries in current date compared to previous date
    Concatenate(DateCompare) Load
    PreviousDate + 1 as Date,
    Country as [Compare Country],
    City as [Compare City],
    'Total' as [Changed Attribute],
    PreviousTotal as [Previous day value] 
    Resident PreviousDateData WHERE Len(CurrentDate)=0;
    
    // 2. Find entries in current date that have changed Type values compared to previous date
    Concatenate(DateCompare) Load
    PreviousDate + 1 as Date,
    Country as [Compare Country],
    City as [Compare City],
    'Type' as [Changed Attribute],
    PreviousType as [Previous day value],
    CurrentType as [Current day value]
    Resident PreviousDateData WHERE PreviousType <> CurrentType AND Len(PreviousType) > 0 AND Len(CurrentType) > 0;
    
    DROP Table PreviousDateData;
Next i;

DROP Table UniqueDates;

Data model and value selection in the dashboard:

Hope this helps,

BR,

Vu Nguyen

[rajasekhar6067]

Thanks much Vu Nguyen! Your solution works perfect!!
You made my day!!

Cheers,
Rajasekhar