Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
sarunome
Contributor
Contributor

How to display the most recent entries by selection for each id

Hello,

I have a complex (maybe only for me) situation. The sample data in a straigth table is:

idcontract_identry_datesign_datechange_datesign_flagchange_flag
112017-09-112017-09-11 10
222019-01-012018-08-282019-01-0101
322018-08-282018-08-28 10
432019-05-062019-05-06 10
542015-01-062015-01-062015-01-0601
642017-01-012015-01-062017-01-0101
742020-01-012015-01-062020-01-0101
842015-01-062015-01-06 10
952007-03-222007-03-22 10

It is kind of self explanatory. entry_date has values of change_date if it is not null or sign_date value if change_date is null. contract_id 4 (and it is no mistake) has been signed and changed on the same day, so it has two entries for 2017-12-30.

On the side there is a calendar created for filtering. If the analyst selects, for example, 2017-12-30 - the table needs to show the most recent entry_date for each contract_id that is not greater than the selection.

So, for the selection of 2017-12-30, the table needs to look like this:

idcontract_identry_datesign_datechange_datesign_flagchange_flag
112017-09-112017-09-11 10
642017-01-012015-01-062017-01-0101
952007-03-222007-03-22 10

Only one entry per contract needs to be displayed. If the analyst selects 2015-01-06, the table needs to look like this:

idcontract_identry_datesign_datechange_datesign_flagchange_flag
542015-01-062015-01-062015-01-0601
952007-03-222007-03-22 10

This needs to be done in expressions, since it is based on selection. Any help would be appreceated.

1 Solution

Accepted Solutions
JGMDataAnalysis
Creator III
Creator III

The [Date] field belongs to the island table [IslandDate]. The selections that the user makes in [Date] do not actually propagate to the [Test] table because there is no field that relates both tables. The maximum value of the [Date] field is used to "filter" the values ​​of the [entry_date] field using set analysis (I attached the qvf file).

If you want to solve the problem using a calculated dimension, you could replace the [id] field with the following...

=If(Aggr(NoDistinct FirstSortedValue({<[entry_date] = {"<=$(=Max([Date]))"}>} Distinct [id], -[entry_date]), [contract_id]) = [id], [id])

QlikCommunityChallenge.PNG

View solution in original post

4 Replies
JGMDataAnalysis
Creator III
Creator III

Try with this...

Test Script:

 

SET NullInterpret = '|';

Test:
NOCONCATENATE 
LOAD * INLINE [
    id, contract_id, entry_date, sign_date,  change_date, sign_flag, change_flag
    1,	1,			 2017-09-11, 2017-09-11, |,	 		  1,		 0
    2,	2,			 2019-01-01, 2018-08-28, 2019-01-01,  0,		 1
    3,	2,			 2018-08-28, 2018-08-28, |,			  1,		 0
    4,	3,			 2019-05-06, 2019-05-06, |,	 		  1,		 0
    5,	4,			 2015-01-06, 2015-01-06, 2015-01-06,  0,		 1
    6,	4,			 2017-01-01, 2015-01-06, 2017-01-01,  0,		 1
    7,	4,			 2020-01-01, 2015-01-06, 2020-01-01,  0,		 1
    8,	4,			 2015-01-06, 2015-01-06, |,	 		  1,		 0
    9,	5,			 2007-03-22, 2007-03-22, |,	 		  1,		 0
];

IslandDate: // The user must select a date in this field from this island table.
NOCONCATENATE
LOAD Date(MinDate + IterNo(), 'YYYY-MM-DD') AS Date
WHILE MinDate + IterNo() < MaxDate
;
LOAD MIN(entry_date) - 1 AS MinDate, MAX(entry_date) + 1 AS MaxDate
RESIDENT Test;

 

Measure:

 

Count({<[id] = {$(=Concat(Aggr(FirstSortedValue({<[entry_date] = {"=$(=Max([Date]))"}>} Distinct [id], -[entry_date]), [contract_id]), ', '))}>} Distinct [id])

 

Result:

clipboard_image_0.png

clipboard_image_1.png

sarunome
Contributor
Contributor
Author

JGMDataAnalysis, thank You for the answer. But is the measure formula ok? Everyting loads fine. Without any selections on the Date field - the overall most recent entries for each contract_id are displayed. But when I try to make selections - nothing happens. It is as if the measure formula does not react to the selections made on the Date field.

Also, would there be a way to create a dimension in place of entry_date field to display the most recent entry dates for each contract_id based on selection on the Date field and leave all other values for that dimension null? Then it would be possible to deselect the "Include null values" option and it would work too without additional count measure. I was trying to achieve this, but the furthest I got was only repeating most recent entry dates for each contract_id entry.

JGMDataAnalysis
Creator III
Creator III

The [Date] field belongs to the island table [IslandDate]. The selections that the user makes in [Date] do not actually propagate to the [Test] table because there is no field that relates both tables. The maximum value of the [Date] field is used to "filter" the values ​​of the [entry_date] field using set analysis (I attached the qvf file).

If you want to solve the problem using a calculated dimension, you could replace the [id] field with the following...

=If(Aggr(NoDistinct FirstSortedValue({<[entry_date] = {"<=$(=Max([Date]))"}>} Distinct [id], -[entry_date]), [contract_id]) = [id], [id])

QlikCommunityChallenge.PNG

sarunome
Contributor
Contributor
Author

Aha, now I have located the problem with the # IDs formula in Your first post. It did not have the lesser than (<) symbol in the:

 

...{"=$(=Max([Date]))"}>}...

 

 Other than that, both solutions now work perfectly fine, but I will be using the second one because it is more aesthetically pleasing for the end user with a little twist in the if statement:

 

=If(Aggr(NoDistinct FirstSortedValue({<[entry_date] = {"<=$(=Max([Date]))"}>} Distinct [id], -[entry_date]), [contract_id]) = [id], [entry_date])

 

Now I will be able to use this dimension instead of the original entry_date field.Thank You very much - You are a pro. 🙂