Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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'
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!
Have a look at FirstSortedValue()
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.
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
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);
Thanks Jontydkpi,
when loading, it errors saying Invalid Expression.
Do we have any other solutions? I am still struggling with this? Thanks
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);
You sir are the real MVP!
Thank you!
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