9 Replies Latest reply: May 5, 2016 5:53 AM by Isaac Paz RSS

    Union of two tables

    Isaac Paz

      Hi,

       

      In my script , I realize the load of two tables (A and B ) with the same fields ( Machine [ PK ] ,Function).
      My idea is to create a new table ( C ) as follows :

      C=A+ B(A.machine!=B.Machine)

       

      example:

      A

      MachineFunction
      1T
      2Y
      3Z

       

      B

      MachineFunction
      1C
      2E
      3P
      4S
      5B

       

      C

      MachineFunction
      1T
      2Y
      3Z
      4S
      5B

       

      Any solution?


      Thanks.


        • Re: Union of two tables
          Gysbert Wassenaar

          Like this:

           

          C:

          LOAD

               Machine,

               Function

          FROM

               A

               ;

           

          LOAD

               Machine,

               Function

          FROM

               B

          WHERE

               Not Exist(Machine)

               ;

          • Re: Union of two tables
            Sunny Talwar

            May be this:

             

            Table:

            LOAD Machine,

                      Function

            FROM A;

             

            Concatenate (Table)

            LOAD Machine,

                      Function

            From B

            Where not Exists (Machine);

            • Re: Union of two tables
              Isaac Paz

              >Hi, thanks for the answers but doing what you say in Tabla A are only rows of A,and none of B.

              this is my Table A:

              PRODUCCION_TIMELINE_COM_INI_EST:

                 load

                codigoOP_PTCIE,

                maquina_PO_PTCIE,

                fechaInicioReal_PTCIE,

                fechaFinReal_PTCIE,

                item_PO_PTCIE,

                CodigoItem_PTCIE,

                fechaInicioVirtual_PTCIE,

                fechaFinVirtual_PTCIE,

                rendimiento_PO_PTCIE,

                eficiencia_PO_PTCIE,

                completada_PO_PTCIE,

                fechaInicioEstimada_PO_PTCIE,

                fechaFinEstimada_PO_PTCIE,

                cantidadProducida_PO_PTCIE,

                fecha_PO_PTCIE,

                duracion_PTCIE

              resident PRODUCCION_TIMELINE_AUX_COM_INI_EST;

               

              and my table B:

               

              concatenate (PRODUCCION_TIMELINE_COM_INI_EST)

              load codigoOP_PTCIE,

                maquina_PO_PTCIE,

                fechaInicioReal_PTCIE,

                fechaFinReal_PTCIE,

                item_PO_PTCIE,

                CodigoItem_PTCIE,

                fechaInicioVirtual_PTCIE,

                fechaFinVirtual_PTCIE,

                rendimiento_PO_PTCIE,

                eficiencia_PO_PTCIE,

                completada_PO_PTCIE,

                fechaInicioEstimada_PO_PTCIE,

                fechaFinEstimada_PO_PTCIE,

                cantidadProducida_PO_PTCIE,

                fecha_PO_PTCIE,

                duracion_PTCIE

              resident MAQUINAMOLDE

              where not Exists(maquina_PO_PTCIE);

               

              ' PRODUCCION_TIMELINE_AUX_COM_INI_EST' and    'MAQUINAMOLDE' are deleted after creating each table.

              what am I doing wrong?



                • Re: Union of two tables
                  Gysbert Wassenaar

                  PRODUCCION_TIMELINE_AUX_COM_INI_EST' and    'MAQUINAMOLDE' are deleted after creating each table.

                  Tables are not simply deleted after loading data from them. Perhaps your script is deleting them with DROP TABLE statements. Or perhaps you're later joining those tables to other tables. Without looking at the entire script there's no telling what happens. Perhaps you can attached the entire script in a text file or as part of a qlikview document.

                • Re: Union of two tables
                  Isaac Paz

                  Hi

                   

                  I created a document with the script.

                  Thanks.

                    • Re: Union of two tables
                      Gysbert Wassenaar

                      Your script contains instructions to drop those tables:

                       

                      drop table PRODUCCION_TIMELINE_AUX_COM_INI_EST;

                      drop table  MAQUINAMOLDE;

                       

                      That's why they don't exist after the script finishes

                        • Re: Union of two tables
                          Isaac Paz

                          What I do is delete the two tables where they came from A and B, not A and B, to avoid synthetic keys.

                          A is "PRODUCCION_TIMELINE_COM_INI_EST" and comes from "PRODUCCION_TIMELINE_AUX_COM_INI_EST".

                          B has no name and comes from  "MAQUINAMOLDE".

                           

                          When I concatenate without the clause " where not Exists ( maquina_PO_PTCIE ) " A is filled with all B , so I do not think the problem is to remove the mother tables.


                          Greetings and thanks again.


                            • Re: Union of two tables
                              Gysbert Wassenaar

                              where not Exists ( maquina_PO_PTCIE )  does not work because all the values already exists in a table because your loading those values from resident tables.


                              Try making a new copy of that field:

                              PRODUCCION_TIMELINE_COM_INI_EST:

                                 load

                                codigoOP_PTCIE,

                                maquina_PO_PTCIE,

                                maquina_PO_PTCIE as CHECK_maquina_PO_PTCIE,

                                fechaInicioReal_PTCIE,

                                fechaFinReal_PTCIE,

                                item_PO_PTCIE,

                                CodigoItem_PTCIE,

                                fechaInicioVirtual_PTCIE,

                                fechaFinVirtual_PTCIE,

                                rendimiento_PO_PTCIE,

                                eficiencia_PO_PTCIE,

                                completada_PO_PTCIE,

                                fechaInicioEstimada_PO_PTCIE,

                                fechaFinEstimada_PO_PTCIE,

                                cantidadProducida_PO_PTCIE,

                                fecha_PO_PTCIE,

                                duracion_PTCIE

                              resident PRODUCCION_TIMELINE_AUX_COM_INI_EST;

                              Use that new copy in the where not exists:

                              concatenate (PRODUCCION_TIMELINE_COM_INI_EST)

                              load codigoOP_PTCIE,

                                maquina_PO_PTCIE,

                                fechaInicioReal_PTCIE,

                                fechaFinReal_PTCIE,

                                item_PO_PTCIE,

                                CodigoItem_PTCIE,

                                fechaInicioVirtual_PTCIE,

                                fechaFinVirtual_PTCIE,

                                rendimiento_PO_PTCIE,

                                eficiencia_PO_PTCIE,

                                completada_PO_PTCIE,

                                fechaInicioEstimada_PO_PTCIE,

                                fechaFinEstimada_PO_PTCIE,

                                cantidadProducida_PO_PTCIE,

                                fecha_PO_PTCIE,

                                duracion_PTCIE

                              resident MAQUINAMOLDE

                              where not Exists(CHECK_maquina_PO_PTCIE,maquina_PO_PTCIE);

                              And finally drop that field

                              DROP FIELD CHECK_maquina_PO_PTCIE;

                                • Re: Union of two tables
                                  Isaac Paz

                                  Hi Gysbert,

                                   

                                  I tried your solution , and the result is  correct but  maquina_PO_PTCIE field from the table A is null.


                                  Thanks.


                                  Edit:

                                   

                                  Finally  I created another table adding value from CHECK_maquina_PO_PTCIE to maquina_PO_PTCIE in the case that
                                  maquina_PO_PTCIE was null , and removed the CHECK_maquina_PO_PTCIE field and the rest of the tables.
                                  It was not a clean process , but it worked.