Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Join “Do More With Qlik” with Mike Tarallo to get a first-hand look at the New Authoring Experience, June 29th: REGISTER HERE
cancel
Showing results for 
Search instead for 
Did you mean: 
rajasekhar6067
Contributor II
Contributor II

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

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.

DateCountryCityTypeTotal
11/15/2019USNewYorkOpen30
11/15/2019USWashingtonOpen45
11/15/2019USDallasOpen100
11/15/2019INDMumbaiOpen75
11/15/2019INDHyderabadOpen63
11/16/2019USNewYorkOpen32
11/16/2019USWashingtonOpen45
11/16/2019INDMumbaiOpen75
11/16/2019INDDelhiOpen99
11/17/2019USSan JoseOpen55
11/17/2019USNewYorkOpen32
11/17/2019USWashingtonClose45
11/17/2019INDDelhiOpen99

 

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

CountryCityChanged AttributePrevious day valueCurrent day value
INDMumbaiTotal75 
USWashingtonTypeOpenClose

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

CountryCityChanged AttributePrevious day valueCurrent day value
INDHyderabadTotal63 
USDallasTotal100 

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.

Labels (1)
1 Solution

Accepted Solutions
vunguyenq89
Creator III
Creator III

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:

test.png

Hope this helps,

BR,

Vu Nguyen

View solution in original post

2 Replies
vunguyenq89
Creator III
Creator III

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:

test.png

Hope this helps,

BR,

Vu Nguyen

rajasekhar6067
Contributor II
Contributor II
Author

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

Cheers,
Rajasekhar