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: 
JustinDallas
Specialist III
Specialist III

Restrict selection to possible (but not P()) values

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.

PartyCity.png

1 Solution

Accepted Solutions
agigliotti
Partner - Champion
Partner - Champion

try with a table object as below:

dimension: aggr( only( {1< ParentOrder = P(OrderNumber) >} OrderNumber ), OrderNumber)

measure: only( {1} BillTo )

View solution in original post

6 Replies
OmarBenSalem

for your childDimension, do as follow:

aggr(Only({$<childDimension= p({1<ParentDimension=>})>} childDimension),childDimension)


and see what happens, when you select a parent dimension

agigliotti
Partner - Champion
Partner - Champion

I think you should do what below:

  1. Orders: 
  2. LOAD * Inline 
  3. OrderNumber, ParentOrder, BillTo, Line, IsParentOrder, IsChildOrder 
  4.     1000, 1000  ,  Starbucks, Coffee Cups , 1 
  5.     2000, 1000, Party City, Party Hats,,1 
  6.     3000, 3000 ,  Lexmark, Printer Ink,1 
  7.     4000, 3000, Oracle, Invoices,,1 
  8.     5000, 5000,  Microsoft, Apple Baskets,, 
  9. ;

  10. and use ParentOrder as dimension
JustinDallas
Specialist III
Specialist III
Author

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.

JustinDallas
Specialist III
Specialist III
Author

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.

PartyCity2.png

agigliotti
Partner - Champion
Partner - Champion

try with a table object as below:

dimension: aggr( only( {1< ParentOrder = P(OrderNumber) >} OrderNumber ), OrderNumber)

measure: only( {1} BillTo )

JustinDallas
Specialist III
Specialist III
Author

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 ) ?