5 Replies Latest reply: May 5, 2012 3:03 AM by Juan Lopez RSS

    Making relations with different tables based on data from another table

    Juan Lopez

      Hi people!

       

       

      so, here's the problem:

       

      in the model I'm trying to build there is a relation which I think is kind of complicated. Movement, has a relation to Location via the idUbicacionConsumo OR the idUbicacionEntrega fields....

       

      the condition which establishes with which field is the former relation created, is %KEY_product_type => if it is 1 or 3, the relation is to be created with idUbicacionConsumo (in the Movement table) and %KEY_location (in the Location table)

       

       

      ELSE

       

      the relation is to be created with idUbicacionEntrega (in the Movement table) and %KEY_location (in the Location table)

       

       

      trickyrelation.png

       

       

       

      thank you for any time you can spare in this issue!

        • Making relations with different tables based on data from another table
          Luciano Muñoz

          Hope this help you:

           

           

          Movement_tmp:

          LOAD %KEY_movement,

                    %KEY_persona,

                    IdUbicacionConsumo,

                    IdUbicacionEntrega

          From XXXXX;

          Left Join

          LOAD %KEY_movement,

                    %KEY_product

          Resident Invoice_detail;

           

          Movement:

          LOAD %KEY_movement,

                    %KEY_persona,

                    If(%KEY_product = 1 or %KEY_product = 3,

                         IdUbicacionConsumo,

                         IdUbicacionEntrega

                    ) as  %KEY_location

                    IdUbicacionConsumo,

                    IdUbicacionEntrega

          Resident Movement_tmp;

           

          DROP Table Movement_tmp;

           

           

           

          Regards.-

          • Making relations with different tables based on data from another table
            Luciano Muñoz

            Yes, in the FROM statement put the route to your qvd.

             

            Movement_tmp is the table created in first place. This table must be created after the table Invoice_detail because make a join with that table.

             

            Movement_tmp:

            LOAD %KEY_movement,

                      %KEY_persona,

                      IdUbicacionConsumo,

                      IdUbicacionEntrega

            From XXXXX;

            Left Join

            LOAD %KEY_movement,

                      %KEY_product

            Resident Invoice_detail;

             

             

            Later create the final table Movement. I forgot to put sentence "NoConcatenate".

             

             

            Movement:

            NoConcatenate

            LOAD %KEY_movement,

                      %KEY_persona,

                      If(%KEY_product = 1 or %KEY_product = 3,

                           IdUbicacionConsumo,

                           IdUbicacionEntrega

                      ) as  %KEY_location

                      IdUbicacionConsumo,

                      IdUbicacionEntrega

            Resident Movement_tmp;

             

             

             

            After all drop temporary table:

             

             

             

            DROP Table Movement_tmp;

             

             

             

            This must function. Sorry for my bad english.

            Luciano.-

            • Making relations with different tables based on data from another table
              Luciano Muñoz

              Hi Juan Manuel, could you solve your problems? In this case please mark this post as answered, because help other people who can have the same problem.

               

              If not solve your issue continue trying to help.

               

              Regards.-