Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
expected values,
once model has changed. 768.86 * 3 = 2306.58. 864.20*3 = 2592
Any help would be appreciated,
thanks
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.
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