topic Re: Display only latest content of a text field based on 2 criteria in QlikView

Display only latest content of a text field based on 2 criteria

PaReD_SF85 — Fri, 20 Nov 2020 18:58:42 GMT

I am new to QlikView so appreciate any suggestions in advance for a solution.

I have 2 tables:

Scan_Details has multiple date entries for each of 2 scan types. The scan dates are not always the same date. Scan_Insights has all scan dates with summary comments (text) for each scan type.

In the script, I have created 2 fields that contain the most recent scan date for each type: MostRecentLXR1Date and MostRecentACB22Date. (In case that should make things easier!)

Desired result: I need to display only the most recent Scan Insights for each scan type. And should it be in a table, text box or other? I have tried several ways but nothing is working. Currently, the use of ONLY results in a hyphen (-).

Scan_Details:

ScanDate	Scan Type
2/19/2020	LXR1
2/19/2020	LXR1
2/19/2020	LXR1
3/19/2020	LXR1
3/19/2020	LXR1
4/21/2020	LXR1
4/21/2020	LXR1
4/21/2020	LXR1
5/26/2020	LXR1
5/26/2020	LXR1
7/2/2020	LXR1
7/2/2020	LXR1
8/2/2020	LXR1
8/2/2020	LXR1
8/2/2020	LXR1
8/2/2020	LXR1
9/1/2020	LXR1
9/1/2020	LXR1
9/1/2020	LXR1
9/1/2020	LXR1
3/26/2020	ACB22
3/26/2020	ACB22
3/26/2020	ACB22
3/26/2020	ACB22
4/7/2020	ACB22
4/7/2020	ACB22
4/7/2020	ACB22
6/18/2020	ACB22
8/2/2020	ACB22
8/2/2020	ACB22
8/2/2020	ACB22
9/1/2020	ACB22
9/1/2020	ACB22

Scan_Insights:

InsightsDataScanDate	Scan Insights Type	Scan Insights
2/19/2020	LXR1	This initial LXR1 scan summary of observations.
2/19/2020	ACB22	The initial ACB22 scan sets the foundation for future scans.
3/19/2020	ACB22	March findings and comments.
5/26/2020	LXR1	On 5/26 spike was realized.
8/21/2020	ACB22	This ACB22 summary ….
9/1/2020	LXR1	This summary will share current activity in the LXR1 space.

Re: Display only latest content of a text field based on 2 criteria

MayilVahanan — Thu, 19 Nov 2020 03:30:54 GMT

Hi @PaReD_SF85

Try like below

Scan_Details:
LOAD *,ScanDate&'|'&[Scan Type] as Key INLINE [
ScanDate, Scan Type
2/19/2020, LXR1
2/19/2020, LXR1
2/19/2020, LXR1
3/19/2020, LXR1
3/19/2020, LXR1
4/21/2020, LXR1
4/21/2020, LXR1
4/21/2020, LXR1
5/26/2020, LXR1
5/26/2020, LXR1
7/2/2020, LXR1
7/2/2020, LXR1
8/2/2020, LXR1
8/2/2020, LXR1
8/2/2020, LXR1
8/2/2020, LXR1
9/1/2020, LXR1
9/1/2020, LXR1
9/1/2020, LXR1
9/1/2020, LXR1
3/26/2020, ACB22
3/26/2020, ACB22
3/26/2020, ACB22
3/26/2020, ACB22
4/7/2020, ACB22
4/7/2020, ACB22
4/7/2020, ACB22
6/18/2020, ACB22
8/2/2020, ACB22
8/2/2020, ACB22
8/2/2020, ACB22
9/1/2020, ACB22
9/1/2020, ACB22
];

Scan_Insights:
LOAD InsightsDataScanDate & '|'& [Scan Insights Type] as Key, * INLINE [
InsightsDataScanDate , Scan Insights Type , Scan Insights
2/19/2020, LXR1, This initial LXR1 scan summary of observations.
2/19/2020, ACB22, The initial ACB22 scan sets the foundation for future scans.
3/19/2020, ACB22, March findings and comments.
5/26/2020, LXR1, On 5/26 spike was realized.
8/21/2020, ACB22, This ACB22 summary ….
9/1/2020, LXR1, This summary will share current activity in the LXR1 space.
];

Join(Scan_Details)
Load [Scan Insights Type] as [Scan Type], Date(Max(InsightsDataScanDate)) as ScanDate, 1 as Flag Resident Scan_Insights group by [Scan Insights Type];

Front end:

In Text box:

=only({<Flag={1}, [Scan Type]={'LXR1'}>}[Scan Insights])

Straight table:

Dim: [Scan Type]

Exp: =Only({<Flag = {1}>} [Scan Insights])

Hope it helps

Re: Display only latest content of a text field based on 2 criteria

PaReD_SF85 — Thu, 19 Nov 2020 19:40:18 GMT

Thank you so much MayilVahanan! This worked. I appreciate your expertise and quick response to my issue.