Subquery in OBIEE

Question

Dear All,

I have a requirement where I need to display the value of a measure based on the maximum submit order. Below is the scenario.

Here against the invno 469 there are total of 3 rows. Each row represents the level in which the invoice was resubmitted by the Insurance team. My requirement is that, in answers I need to display the Resubmitted_Amount based on the last submit order (In this case 3, but it can change based on the number of resubmissions done for a particular invoice. Sometimes it can be 4 submissions or 10 submissions). I could acheive this in SQL as below

select invno,resubmittable_amt from tby_bi_dwh.tgt_insurance_inv_stmt a where a.invno = 469 and a.submit_order

=(select max(submit_order) from tby_bi_dwh.tgt_insurance_inv_stmt where invno = a.invno) ;

Can anyone suggest how the result can be acheived in BI answers ? Any help would be appreciated.

Regards

Vyshak P

[Deleted User] · Answer

I was praying that your screenshot was just a dirty mockup and you have actual data models...

Silly me

Gianni Ceresa · Answer

Instead of MAX, use a reversed RANK by INVNO, so you can filter on this new column = 1 to get the highest SUBMIT_ORDER by INVNO.

2773333 · Answer

But I am not using the date/time for this requirement. One logic I had in mind is using a custom column in BMM by using MAX(Submit_Order) and use it in the Answer. But how will I apply that in the column formula I am not sure.