Hi. I have 2 queries .
Query 1 returns Order Number, Item ID, and other Order related information.
Query 2 returns Order Number, Payment Date, Payment Status and other Payment related information.
This second query could have multiple lines of date for a single order.
The requirement is to create a table in webi, which has order number(from Query 1), item id(from Query 1), Payment status (from query2).
The payment Status should be the latest- as given by Payment Date.
I have merged on Order Number, and have created detail objects for Payment Status and Payment Date. However, I am not able to bring them all in the same table. I have tried running count, max,rank to get the payment status with latest date in the same table as other order info. But no success. Always end up with multivalue error or all the rows of payment.
Welcome to the boards. I think you have misunderstood the concept of details.
Details are expected to have a one-to-one relationship with their owning dimension. In your case, a Payment Status does not have a one-to-one relationship with an order.
To achieve what you want, you need to get familiar with the functionality of Max and Where, as well as the concept of ForEach and In (these are known as calculation contexts).
Max is the same as max functionality in other tools, i.e. =Max([Payment Date])
Where is exactly that, i.e. =sum([Sales]) where ([Sales Year] = 2021)
ForEach allows you to include items in your calculation that are not in the block, evaluating a single row as though it is a multi-row - in your case, one Order with multiple payments.
In allows similar, but just evaluates based on the results set - =Max([Payment Date])
Create a block with =[Order Number] and =sum([Order Value])
Add another column with =Max([Payment Date]) and you’ll see the max payment date for that order.
All good so far.
Add another column with =Max([Payment Status]) and you’ll see the max alphabetical Payment Status for the order, which obviously isn’t what you want.
What you’ll need is (approximately) =[Payment Status] Where ([Payment Date] = Max([Payment Date] in [Order Number]) ForEach ([Payment Date])
The positioning of brackets has varied down the versions and I don’t currently have access to Webi so you’ll have to experiment a little bit. The above should be enough to get you sorted though.
Thank you so much. I went with a version of your formula, as it was throwing a #multivalue error, since(I am assuming) there are multiple Payment Date, and so when considering it in the where clause, BO is unable to handle it.
Formula used: =[Payment Status] Where ( Max([Payment Date]) =( Max([Payment Date]) In ([Order Number])) )
Another issue I faced was that, this formula worked fine, when I only used Order Number, and other measures from Query 1.
As soon as I added any dimension from Query 1, the new formula stopped working. It did not return any values. So, I created detail objects for all other Dimensions from Query 1, based on Order Number.
It should work for now, as long as Query 1 does not return multiple values for each Order Number.