10 Replies Latest reply: Aug 28, 2017 2:14 AM by Ibrahim Aslan RSS

    Join or Concatenate!?

    Ibrahim Aslan

      Hi Friends,

       

      I am sitting on the computer this morning and therefore I think my brain is not working as it should.

      I have two tables, once header data and once position data and some info data.

      Looks like this:

       

      Header:

       

      OrderMaterialQuantityDate
      Ord123Mat110001.09.2017

       

       

      Position:

       

       

      OrderWorkcenterDatehours
      Ord123W131.08.20171
      Ord123W230.08.20172

       

       

      Mat_Data:

       

      MaterialGroup
      Mat1GroupA

       

       

      Wrkc_Data:

       

      WorkcenterName
      W1NameW1
      W2NameW2

       

      The Result should be:

       

       

      OrderMaterialGroupWorkcenterNameDateQuantityhours
      Ord123Mat1GroupAW1NameW101.09.2017100-
      Ord123Mat1GroupAW2NameW201.09.2017100-
      Ord123Mat1GroupAW1NameW131.08.2017-1
      Ord123Mat1GroupAW2NameW230.08.2017-2

       

      You can see in the Result Table that the Quantity is now in both Workcenter, this is correct. But in the Chart I want to see only the Quantity 100 if I select both Workcenter. The sum of Quantity for this order should always be the Quantity in the header, however how much Workcenters are in the Position.

       

      But for the hours it's not the same, here I want to see in the chart the sum of all Workcenters, in this example 3.

       

      If I make a Join, then the Quantity is wrong in the chart. If I make it with Concatenate then for the Quantity the Workcenter is missing.

       

      hope I could explain it well, as I said my head already bursts.

      I thank you in advance.

        • Re: Join or Concatenate!?
          Anil Babu Samineni

          Try to do inner join for all tables to make one table and loaf fields into table box then show the image. I don't have software right now

          • Re: Join or Concatenate!?
            Eduardo DImperio

            Hi Ibrahim,

             

             

            To me the best method in order are: Keep, Join, Concatenate.

             

             

            Keep: like name says will keep all tables, reduce the data and will look like a Join

            Join: Use Left or Right Join, Inner could result in a cartesian with a big and  useless process time

            Concatenate: It join all results and could bring a lot of duplicated values.

             

             

            So in your case use a Left Keep or a Left Join to Header, Position and Wrkc_Data, but like you dont have common field in Math data( this not good cause your join will result in a catersian) use a Concatenate.

             

            This article could help you

             

            Understanding Join, Keep and Concatenate

              • Re: Join or Concatenate!?
                Ibrahim Aslan

                Thank you Eduardo.

                 

                I tried it with Left Keep already, but the problem is, with Left Keep you don't have one table, the result are two tables.

                So it's not possible to make a Resident Tabel with this data or a join of other tables that should be joined on both tables.

                 

                It's not only difficult to solve it, it's even difficult to explain it

                 

                Thanks anyway

                  • Re: Join or Concatenate!?
                    Eduardo DImperio

                    I understand very well, i already had this kind of problem.

                     

                    Do this:

                     

                    Header:

                    Load

                    *

                    FROM 'Header Place';


                    Left Join (Header)

                    Load

                    *

                    FROM 'Position Place';



                    Left Join (Header)

                    Load

                    *

                    FROM 'Wrkc_Data Place';


                    //Now you have 1 table 'Header'


                    Concatenate (Header)


                    Load

                    *

                    FROM 'Mat_Data Place';


                    //I think this could souve your problem

                     



                     



                • Re: Join or Concatenate!?
                  Andrew Walker

                  Hi Ibrahim,

                  Maybe:

                  Header:

                  LOAD * INLINE [

                      Order, Material, Quantity, Date

                      Ord123, Mat1, 100, 01.09.2017

                  ];

                   

                   

                  Left Join

                  LOAD * INLINE [

                       Material, Group

                      Mat1, GroupA

                  ];

                   

                   

                  Position:

                  LOAD * INLINE [

                      Order, Workcenter, PosDate, hours

                      Ord123, W1, 31.08.2017, 1

                      Ord123, W2, 30.08.2017, 2

                  ];

                   

                   

                  Left Join

                  LOAD * INLINE [

                      Workcenter, Name

                      W1, NameW1

                      W2, NameW2

                  ];

                   

                  Gives two tables in the data model and this tablebox:

                   

                  Order Material Group Workcenter Name Date Quantity hours
                  Ord123Mat1GroupAW1NameW101.09.20171001
                  Ord123Mat1GroupAW2NameW201.09.20171002

                   

                  Regards

                   

                  Andrew

                  • Re: Join or Concatenate!?
                    Eduardo DImperio

                    Hi Ibrahim,

                     

                    Did you solve your problem?

                      • Re: Join or Concatenate!?
                        Ibrahim Aslan

                        Hi Eduardo,

                         

                        yes I did. I said before, that I tried it in the way like Andrew wrote.

                        But the problem was, how to join with a mastercalender.

                         

                        At the end, I create a second mastercalender and make a new name for the date. (HeaderDate)

                        In the chart with Quantity I have the second calender, in the chart with hours the first calender.

                        And if I select orders or workcenters in both charts I see the right results. This is the perfect way for me.