topic Re: Union of two tables in App Development

Union of two tables

Mon, 02 May 2016 16:19:07 GMT

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:

Machine	Function
1	T
2	Y
3	Z

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

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

Any solution?

Thanks.

Re: Union of two tables

Gysbert_Wassenaar — Mon, 02 May 2016 16:29:50 GMT

Like this:

LOAD

Machine,

Function

FROM

;

LOAD

Machine,

Function

FROM

WHERE

Not Exist(Machine)

;

Re: Union of two tables

sunny_talwar — Mon, 02 May 2016 16:30:53 GMT

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

Tue, 03 May 2016 08:28:13 GMT

>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 — Tue, 03 May 2016 12:38:14 GMT

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

Wed, 04 May 2016 08:22:57 GMT

I created a document with the script.

Thanks.

Re: Union of two tables

Gysbert_Wassenaar — Wed, 04 May 2016 12:45:24 GMT

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

Wed, 04 May 2016 13:15:34 GMT

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 — Wed, 04 May 2016 16:08:24 GMT

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

Thu, 05 May 2016 08:25:18 GMT

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.