Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
Bsangha12
New Contributor III

Bringing latest value

Hi,

I am pulling in one of the apps I am developing a list of customer details from a SQL table. There are multiple lines in the table for each year. I want to bring through the latest policy for each customer. This would be the Latest 'EffectiveDate'

example.png

 

SMECustomerDetails:
LOAD
CustomerName,
PolicyNumber,
IF(len(PolicyNumber)=11,left(PolicyNumber, 2),'') AS Product,
PolicyAdminSystem,
PolicyStatus,
BusinessSegment,
date(EffectiveDate, 'DD/MM/YYYY') as EffectiveDate,
date(ExpiryDate, 'DD/MM/YYYY') as ExpiryDate,
If(CancellationDate = '01/01/0001', '', date(CancellationDate, 'DD/MM/YYYY')) as CancellationDate,
PolicyTerm
FROM [lib://QVD/DMA\SMECustomerDetails.qvd]
(qvd)
Where date(EffectiveDate,'DD/MM/YYYY') <= date(today(),'DD/MM/YYYY');


Left Join (SMECustomerDetails)
LOAD DISTINCT
PolicyNumber,
ProductCode
FROM [lib://QVD/DMA\DimPolicy.qvd]
(qvd)
Where wildmatch(ProductCode,'*');

 

This is what I currently have. Any help would be greatly appreciated!

Labels (2)
10 Replies
Highlighted
MVP
MVP

Re: Bringing latest value

Have a look at FirstSortedValue() 

Highlighted
srdheekonda
New Contributor III

Re: Bringing latest value

Create a aggregate table with required criteria, For each customer,max(date) with valid criteria and group by the customer.

This aggregate table will have one latest record for each customer.

then join this table with detail table based on Customer Id and Max Date column to get all other details. 

Highlighted
Bsangha12
New Contributor III

Re: Bringing latest value

I have given up trying the FirstSortedValue and the Aggregated table, keeps saying Invalid Dimension or breaks.

I am now going to create a field where it shows '1' where the latest record is held. Wish me luck

Highlighted
MVP
MVP

Re: Bringing latest value

How about this:

Fact:
LOAD Max(EffectiveDate) as EffectiveDate,
	PolicyNumber,
	1 As Latest
FROM [lib://QVD/DMA\SMECustomerDetails.qvd]
(qvd);

Inner Join(Fact)
LOAD
	CustomerName,
	PolicyNumber,
	IF(len(PolicyNumber)=11,left(PolicyNumber, 2),'') AS Product,
	PolicyAdminSystem,
	PolicyStatus,
	BusinessSegment,
	date(EffectiveDate, 'DD/MM/YYYY') as EffectiveDate,
	date(ExpiryDate, 'DD/MM/YYYY') as ExpiryDate,
	If(CancellationDate = '01/01/0001', '', date(CancellationDate, 'DD/MM/YYYY')) as CancellationDate,
	PolicyTerm
FROM [lib://QVD/DMA\SMECustomerDetails.qvd]
(qvd);
Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Highlighted
Bsangha12
New Contributor III

Re: Bringing latest value

Thanks Jontydkpi, 

when loading, it errors saying Invalid Expression. example.JPG

Highlighted
Bsangha12
New Contributor III

Re: Bringing latest value

Do we have any other solutions? I am still struggling with this? Thanks

Highlighted
MVP
MVP

Re: Bringing latest value

The first load needed a Group By:

Fact:
LOAD Max(EffectiveDate) as EffectiveDate,
	PolicyNumber,
	1 As Latest
FROM [lib://QVD/DMA\SMECustomerDetails.qvd]
Group By PolicyNumber
(qvd);
Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Highlighted
Bsangha12
New Contributor III

Re: Bringing latest value

You sir are the real MVP!

Thank you! 

Highlighted
Bsangha12
New Contributor III

Re: Bringing latest value

That is great and now I am able to see the latest policy. 

How would I be able to pull all policies down, and keep the flag of '1' to show the latest record for the latest record? if that makes sense