1 Reply Latest reply: Jun 3, 2009 12:41 AM by John Witherspoon RSS

    Set analysis manipulation

      Hello,

      I have a table like that :

      items : "id_item,id_item_from,item_value"


      where :
      - id_item is the item identifiant
      - id_item_from represent the id_item of another item (it can be 0 if this item is not linked with an other item)
      - item_value is the item value ^^

      I need to traverse items which have not a null id_item_from and then :

      sums the "item_value" of the items which have an "id_item" who is in the list of "id_item_from"


      example :
      id_item,id_item_from,item_value
      1,0,5
      2,0,6
      3,1,9
      4,2,3



      I should have
      => id_item : 3 which have id_item_from = 1 => get the value : 5
      => id_item : 4 which have pour id_item_from = 2 => get the value : 6

      Result : 5+6 = 11

      I tried this :

      Sum({$<id_item={$(=id_item_from)}>} item_value)

       

      It's wrong because "$(=id_item_from)" is the "current" selection ... not the corresponding "id_item_from" of the current traversing "id_item" ...

      Any solution ?

      Thanks in advance.

        • Set analysis manipulation
          John Witherspoon

          Is it OK to handle it in the script? You could join the table back to itself like this:

          [Example]:
          LOAD * INLINE [
          id_item,id_item_from,item_value
          1,0,5
          2,0,6
          3,1,9
          4,2,3
          ];
          LEFT JOIN LOAD
          id_item as id_item_from
          ,item_value as item_value_from
          RESIDENT [Example]
          ;