1 Reply Latest reply: Apr 18, 2013 10:44 AM by Eddy Sanchez RSS

    Nested search or loop

    German Avanzato

      Hi Experts,

       

      I'm trying to determine the price of a product.

      For this task, I need to summarize the cost of all his components.

      Something like this:

       

       

      Product                Component               Cost    

      A                              aa11                        12

      A                              bb32                         1

      A                              cda                           43

      B                              sdf                           13

      B                              bb32                         1

       

      Cost product A = 56

      Cost product B = 14

       

      But in some case, one or more of the component of the product, is another product:

      Product                Component               Cost    

      A                              aa11                        12

      A                              bb32                         1

      A                              cda                           43

      A                              B

      A                              C

      C                              aa11                         12

      C                              sdf                            13

      C                              ccc                            4

      B                              sdf                           13

      B                              bb32                         1                   

      B                               C                             

      B                              hfsdf                         315

      B                              hgf1                         45

       

      Cost Product A = 488

      Cost Product B = 403

      Cost Product C = 29

       

      So in this case, first I need to determinate the price of C, after that; the price of B, and finally the price of A.

      Not always is the same sequence, but always can be solved.

       

       

       

      I don´t know how to approach to this problem in QV.

      Any suggestion?

       

       

      Thanks

        • Re: Nested search or loop
          Eddy Sanchez

          Table:

          LOAD * INLINE [

          Product,                Component,               Cost   

          A,                              aa11,                        12

          A,                              bb32,                         1

          A,                              cda,                           43

          A,                              B,

          A,                              C,

          C,                              aa11,                         12

          C,                              sdf,                            13

          C,                              ccc,                            4

          B,                              sdf,                           13

          B,                              bb32,                         1                  

          B,                               C,                            

          B,                              hfsdf,                         315

          B,                              hgf1,                         45

          ];

           

           

          //Group

          Left Join(Table)

          LOAD Product as Component,

                    Sum(Cost) as ComponentCost

          Resident Table

          Group By Product;

           

           

          FinalTable:

          LOAD Product,

                    Component,

                    If(Cost='',0,Cost) + If(IsNull(ComponentCost),0,ComponentCost) as Cost

          Resident Table;

           

           

          DROP Table Table;