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: 
Not applicable

Expression Help

I have an expression:

SUM( {1} if(OrderBTL >= 2, if(OrderBTR <= 9, Amount)))

that gives me the exact result that I want, except I would like to replace the constants (2 & 9) with fields from the currently selected set. For 2 I would like to use the field called [left] and for 9 I would like to use the field called

. I have tried a number of different things but have been unable to get it right.

I realize that this expression will only be evaluated once for the sheet,

I am open to suggestions for improvement as well.

Thanks in advance

John

14 Replies
JonnyPoole
Employee
Employee

Maybe this is closer:

-----------

PeopleTemp:

HierarchyBelongsTo(PersonKey, ParentKey, Name, AncestorID, AncestorName, DepthDiff)  LOAD * INLINE [

  PersonKey,ParentKey, Name, ChildBranch

    1,,'Bobby'

    2,1,'Bill','Left'

    3,1,'Peter','Right'

    4,2,'Kate','Left'

    5,2,'Lloyd','Right'

    6,4,'John','Left'

    7,3,'Gino','Left'

];

// PersonKey,ParentKey, Name, Branch

People:

Load * , if(PersonKey=AncestorID,'Current Node','Children') as AggregationType

resident PeopleTemp;

drop table PeopleTemp;

Orders:

LOAD * INLINE [

  OrderNum, PersonKey, Amount, OrderLeft, OrderRight

    1,6,100,4,5

    2,6,100,4,5

    3,6,100,4,5

    4,7,500,11,12

    5,3,250,12,13

    6,4,150,3,6

];

--------------------

Capture.PNG

JonnyPoole
Employee
Employee

Was this of any help  ?  I liked the post because it brought an angle that was new to me with respect to hierarchies, that is, a hierarchy with just 2 branches (left and right) at each node and how best to reflect that attribute in the hierarchy load.  I did feel the expression and load script i presented resolved the situation but the one piece that would need to be customized is to transform the data into a format that i used in my hierarchybelongsto load.  anyways , it was interesting so if you get back to this i'd love to hear how things turned out.

Not applicable
Author

I haven’t had a chance to try it out yet, but I will in the next day or so.

I’m glad I was able to challenge you!

Not applicable
Author

Jonathan,

your solution appears to work but for only 1 level down in the tree.

For example, if I choose "Bobby" and "Right" it shows 250, but it should show 750 (Peter's 250 + Gino's 500).

I believe the example works for "Bill" and "Left" because both Kate and John are on the left side. If we were to move John to Kate's right side I bet it will not work.

Thanks again for the attempt. Your approach has given me something else to think about as I work towards a solution.

JonnyPoole
Employee
Employee

John - i see exaclty what you are referring to.  The left/right branch field propogates to all children giving you not only the left branch children but the left branch of those children too (grandchildren) which is wrong for this need.


I'm thinking another attribute is needed to differentiate children vs grandchildren.  I'll take another crack at this when i have some cycles.