Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Union of two tables

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.


1 Solution

Accepted Solutions
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;


talk is cheap, supply exceeds demand

View solution in original post

9 Replies
Gysbert_Wassenaar

Like this:

C:

LOAD

     Machine,

     Function

FROM

     A

     ;

LOAD

     Machine,

     Function

FROM

     B

WHERE

     Not Exist(Machine)

     ;


talk is cheap, supply exceeds demand
sunny_talwar

May be this:

Table:

LOAD Machine,

          Function

FROM A;

Concatenate (Table)

LOAD Machine,

          Function

From B

Where not Exists (Machine);

Not applicable
Author

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


talk is cheap, supply exceeds demand
Not applicable
Author

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


talk is cheap, supply exceeds demand
Not applicable
Author

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

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;


talk is cheap, supply exceeds demand
Not applicable
Author

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.