16 Replies Latest reply: Jul 6, 2011 8:43 AM by Stefan Wühl RSS

    Compare Two Field From 2 Tabels

    Matan Chalamish

      Hello All.

       

      I have this situation:

           2 stright-table. (Row 2 is expression)

       

      PartArc.PartNameStandard value(Kg)Std.value-Prod(Kg)
      Sum(PartArc.Coef * Serial.Quant),(Sum(PartArc.Coef * Serial.Quant)/Serial.Quant) *  Sum(Aline.Quant)
      431.82446.21
      30001003284.1586.96
      300010033252.54260.96
      3000200082.522.60
      3000200121.711.77
      3000300075.045.21
      30004001247.1648.73
      30005000338.7039.99

       

       

      Transorder.PartNameActual value(Kg)Gap(%)
      Sum(Transorder.Quant)(Prod-Act)/Act
      657.8-
      300010032155.24-
      300010033428.07-
      3000200084.04-
      3000200122.7-
      3000300077.91-
      30005000359.84-

       

      What I Want to Show Is:

      PartNameStandard value(Kg)Std.value-Prod(Kg)Actual value(Kg)Gap(%)
      431.82446.21657.8-
      30001003284.1586.96155.2485.955
      300010033252.54260.96428.07259.958
      3000200082.522.604.041.604
      3000200121.711.772.70.767
      3000300075.045.217.914.208
      30004001247.1648.73-
      30005000338.7039.9959.8438.99

       

      How can I do that ( PartName Dimension?) ?

       

      Table View:

      TableWiew.JPG

        

      Thks.

        • Compare Two Field From 2 Tabels
          Stefan Wühl

          Hi plzmatanch,

           

          You want a table with dimension part.partname as first column, right?

          You can use set analysis to limit the expressions to the parcart resp. transorder.partname equal to part.partname:

           

          e.g. Sum( {$<PartArc.ParcName=Part.PartName> } PartArc.Coef * Serial.Quant)

           

          other sum accordingly

           

          Regards,

          Stefan

            • Compare Two Field From 2 Tabels
              Matan Chalamish

              Thank you - Stefan.

               

              My problem is that the part.PartName (the common field) is the "father" of  Both: Transorder.PartName and PartArc.ParName.

               

              I try another way to explain the problem.

              Let say that we bake a cake. the cake is The Part.PartName (Ex: Part.PartName = 1000).
              Now, all the Ingredients de facto are under Transorder.PartName (Ex. Transorder.PartName = 1003, 1005, 1008 ,1033), and compatible quantities at: Sum(Transorder.quant)
              In contrast, all the recipe Ingredients are under PartArc.PartName(Ex. PartArc.PartName = 1003, 1008, 1012, 1022), and compatible quantities at: Sum(PartArc.Coef * Serial.Quant)

              So my problem is to create straight-table (maybe somthing else?)  that include all the part name with compatible expression:

               

              Part     Sum(Transorder.quant)     Sum(PartArc.Coef * Serial.Quant)     Gap

              1003     X1                                          Y1                                                             X1-Y1

              1005     X2                                        

              1008     X3                                          Y2                                                             X3-Y2

              1012                                                    Y3

              1022                                                    Y4

              1033     X4

                  

               

              Hope that it's clearly....

               

              Thanks all!

                • Compare Two Field From 2 Tabels
                  Stefan Wühl

                  Hi plzmatanch,

                   

                  I hope I do understand...

                   

                  Well, I still would go with the "father" part.PartName as dimension and filter the expression with set expression as indicated above. I think this should result in the table you want to have.

                   

                  In your example, what is "Part" if not part.PartName (it must be superset of both other partName sets, right)?

                  I assume also that PartNames are identical in all sets (father and sons), though then the data sets are  redundant and could be errornous.

                   

                  If it is possible, you could post an example .qvw (with limited data set if needed), this might help to understand your problem.

                   

                  Regards,

                  Stefan

                    • Re: Compare Two Field From 2 Tabels
                      Matan Chalamish

                      Hello all

                      and Stefan, Thnks again...

                       

                      I attached simple example, that demonstrate my problem.

                      My problem is to combine the tow Straigth table.

                      Where the result (for "AppleCake") Is:

                       

                      PartName

                      PartDes

                      Sum(PartArc.Coef * Serial.Quant)

                      SUM(Transorder.Quant)

                      Gap (Recipe-Transorder)

                       

                      1359.2

                      2070

                      710.8

                      1008

                      flour

                      166

                      200

                      34

                      1009

                      Sugar

                      249.2

                      670

                      420.8

                      1022

                      Apple

                      860

                      1000

                      140

                      1077

                      Caramel

                      84

                      50

                      -34

                      1033

                      Wine

                      150

                      150

                       


                      Hope It's better.

                      Thaks a lot!

                        • Re: Compare Two Field From 2 Tabels
                          Stefan Wühl

                          Hi plzmatanch,

                           

                          I think I understand now. Thanks for the example.

                           

                          I tried something, but no full solution yet. I added a table with a list of all PartNames (see script in example).

                           

                          I used this to generate a straight table dimension, all ingredients (recipe and actual used) are now listed correctly, also PartDes.

                           

                          I still have a problem with limiting the expressions:

                          - In the attached example, the expressions always calculate the total over all parts in each line, not limiting to the actual part name.

                          I tried around with set expression but no success until now.

                           

                          - If a add the two commented lines in the script (add Links to PartNames in transorder and recipe), I get correct results for the straight table you want, but the data model now has a synthetic key of course, which leads to problems in your recipe ingredients table.

                           

                          Maybe this shows a way (maybe not) to go on, if I have time, I may work on this a bit more later on.

                           

                          Best regards,

                          Stefan