Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

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

Re: Union of two tables

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
9 Replies

Re: Union of two tables

Like this:

C:

LOAD

     Machine,

     Function

FROM

     A

     ;

LOAD

     Machine,

     Function

FROM

     B

WHERE

     Not Exist(Machine)

     ;


talk is cheap, supply exceeds demand
MVP
MVP

Re: Union of two tables

May be this:

Table:

LOAD Machine,

          Function

FROM A;

Concatenate (Table)

LOAD Machine,

          Function

From B

Where not Exists (Machine);

Not applicable

Re: Union of two tables

>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

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

Re: Union of two tables

Hi

I created a document with the script.

Thanks.

Re: Union of two tables

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

Re: Union of two tables

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

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

Re: Union of two tables

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.