Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
QlikBeginner1
Creator
Creator

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
tresesco
MVP
MVP

Have a look at FirstSortedValue() 

srdheekonda
Contributor III
Contributor III

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. 

QlikBeginner1
Creator
Creator
Author

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

jonathandienst
Partner - Champion III
Partner - Champion III

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
QlikBeginner1
Creator
Creator
Author

Thanks Jontydkpi, 

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

QlikBeginner1
Creator
Creator
Author

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

jonathandienst
Partner - Champion III
Partner - Champion III

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
QlikBeginner1
Creator
Creator
Author

You sir are the real MVP!

Thank you! 

QlikBeginner1
Creator
Creator
Author

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