Skip to main content
Announcements
Global Transformation Awards! Applications are now open. Submit Entry
cancel
Showing results for 
Search instead for 
Did you mean: 
PaReD_SF85
Contributor III
Contributor III

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

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:

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

 

Scan_Insights:

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

Accepted Solutions
MayilVahanan

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

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.

View solution in original post

2 Replies
MayilVahanan

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

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
PaReD_SF85
Contributor III
Contributor III
Author

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