topic Re: Compare Previous day data to current day and display records which are missing for current day in App Development

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

rajasekhar6067 — Sat, 16 Nov 2024 04:02:38 GMT

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.

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

vunguyenq89 — Mon, 25 Nov 2019 14:27:44 GMT

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

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

rajasekhar6067 — Tue, 26 Nov 2019 09:27:53 GMT

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

Cheers,
Rajasekhar