4 Replies Latest reply: Apr 18, 2013 1:28 PM by German Avanzato 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;

            • Re: Nested search or loop
              German Avanzato

              This is useful but not quite right.

              I think in that solution, but the problem is the result ist wrong:

              Product Costo
              A 459
              B 403
              C 29

               

              Because when you try to determinate the cost of A, the cost of B is 374 because the C is not yet summarize. Its like you need to repeat this step until all Product that are Components has his cost.

            • Re: Nested search or loop
              Gysbert Wassenaar

              Sounds like a BOM (Bill of Materials) problem. See here for an example of how to deal with that.