Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
QlikBeginner1
Creator
Creator

How to get the latest 'status' of a record by the latest Date?

Hello,

The ultimate goal that I was trying to do was to be able to get the latest 'status' of a record by the latest Date.

I have a 'Header' Table. 

I have joined the Transaction tables to the header tables. This was working fine.

 

LEFT JOIN [Header]:

LOAD

PNumber,  
Date(Max(TransactionEffectiveDate,'DD/MM/YYYY')) as TransEffDate,
1 as TransEffDateFlag
RESIDENT Header
GROUP BY PNumber;


[HeaderMain]:

NoConcatenate

LOAD
*,
If([TransEffDate]=[TransactionEffectiveDate], 1, 0) as "MaxTransFlag"
RESIDENT Header;
DROP TABLE Header;

 

I added this into the data load editor. The flag is working correctly, and is flagging the Max Transaction Date that I was expecting. However the issue I am getting is now the premium has multiplied by three. Can you see from the above why the model has X3 the premiums from the logic I have included above? Previously, I wouldnt have included the left join and the max date of the transaction date grouped by policy, however this was the only way I could achieve the Flag for the latest record. I have dropped the Header table as well, cant see what I am doing wrong?

QlikBeginner1_1-1629768266334.png

expected values,

 

QlikBeginner1_2-1629768287239.png

once model has changed. 768.86 * 3 = 2306.58.   864.20*3 = 2592

Any help would be appreciated,

thanks

2 Replies
Digvijay_Singh

Whats the expression you have used in the straight table? Also check if date is not having a decimal part, it might not work properly in one of the IF conditions above.

Also before you create HeaderMain, try to create a test straight table and add all the old and new fields and make some selections, I think it would help you to identify the root cause behind the problem.

Just some ideas shared, if you could share sample app, I am sure someone can help to identify exact cause.

 

anthonyj
Creator III
Creator III

Hi,
I agree with @Digvijay_Singh . Try loading the straight Header table first without the left join and make sure your values are what you expect. This will point out if the issue is further up the code.

If you create the table you're after in the visualisation and then add a "FirstSortedOrder( )" function you can get the latest status of each of the PNumber:

Add PNumber as a dimension and then add as a measure:

FirstSortedValue(Status, -TransactionEffectiveDate)

If you want it as a dimension you can do so by adding the aggregation like this:

aggr(FirstSortedValue(Status, -TransactionEffectiveDate), PNumber)

I hope  this helps.

Thanks

Anthony