Union of two tables

[#1]

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

Machine	Function
1	T
2	Y
3	Z

B

Machine	Function
1	C
2	E
3	P
4	S
5	B

C

Machine	Function
1	T
2	Y
3	Z
4	S
5	B

Any solution?

Thanks.

[Gysbert_Wassenaar]

Like this:

C:

LOAD

     Machine,

     Function

FROM

     A

     ;

LOAD

     Machine,

     Function

FROM

     B

WHERE

     Not Exist(Machine)

     ;

[sunny_talwar]

May be this:

Table:

LOAD Machine,

          Function

FROM A;

Concatenate (Table)

LOAD Machine,

          Function

From B

Where not Exists (Machine);

[#4]

>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?

[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.

[#6]

Hi

I created a document with the script.

Thanks.

[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

[#8]

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.

[Gysbert_Wassenaar · Accepted solution]

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;

[#10]

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.