Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
EDIT: I messed up the image below and took the screenshot before changing the OrderNumber values. But the point still stands.
Hello Everyone,
I have a datamodel that looks like this.
Orders:
LOAD * Inline
[
OrderNumber, ParentOrder, BillTo, Line, IsParentOrder, IsChildOrder
1000, , Starbucks, Coffee Cups , 1
2000, 1000, Party City, Party Hats,,1
3000, , Lexmark, Printer Ink,1
4000, 3000, Oracle, Invoices,,1
5000, , Microsoft, Apple Baskets,,
]
;
As you can see, there is a ParentChild relationship between the rows of the datatable. What I would like to do, is have a table of just Parent Orders, and then have a table of just Children Orders. When the user selects the parent, the Children table will just show those attached children.
I'm not sure how to accomplish this because when you select an OrderNumber, you exclude all the other OrderNumbers, including the children OrderNumbers. In set analysis, you would do something like SUM{1<ParentOrderNumber=P(OrderNumber)>} (I think). But when taking this idea and using it as a Dimension, I'm at a standstill.
Any help is greatly appreciated.
Below is an image of what I would like to be able to do.
try with a table object as below:
dimension: aggr( only( {1< ParentOrder = P(OrderNumber) >} OrderNumber ), OrderNumber)
measure: only( {1} BillTo )
for your childDimension, do as follow:
aggr(Only({$<childDimension= p({1<ParentDimension=>})>} childDimension),childDimension)
and see what happens, when you select a parent dimension
I think you should do what below:
Hello Andrea,
I did use the ParentOrder as a dimension, but not every Order is going to be a Parent or Child. Some Orders stand alone.
Hello Omar,
I think this is close (mostly because I don't understand the whole thing), but it's not quite working and I don't know how to diagnose it. Using Andrea Gigliotti's LOAD INLINE statement, when I select Starbucks, I should get Party CIty as a BillTo and 2000 as the OrderNumber. However, when I do that, all I get is a Child Table with Starbucks in it.
The current piece I have is:
aggr(Only({$<OrderNumber= P({1<ParentOrder=>})>} OrderNumber),OrderNumber)
What I think it's saying is
For the selected dataset, the OrderNumber should be in the Possible dataset or where the ParentOrder is "something". If there is only one, return the OrderNumber.
I'm not sure what the Aggr is doing.
try with a table object as below:
dimension: aggr( only( {1< ParentOrder = P(OrderNumber) >} OrderNumber ), OrderNumber)
measure: only( {1} BillTo )
Thanks Andrea, this worked, but why?
I understand the part about "{1< ParentOrder = P(OrderNumber) >} " because that says (If the Parent Order is a member of all Possible Orders in the entire dataset". But the reason behind the Only (return only one parent or return NULL) and the use of the AGGR befuddles me.
Thanks for your help.
Also, why do we need the measure of Only( {1} BillTo ) ?