topic Re: Simple join of two resident tables in QlikView

Simple join of two resident tables

intervigilium — Wed, 25 Nov 2020 16:16:04 GMT

I have some data that i "convert" using crosstable functionality.

The result is 2 resident tables which i want to combine. Both tables have 8 the same fields en 2 different.

Normally i would expect a regular (outer) join would do the trick, but nothing happens...

Tussentabel1:

LOAD Land,

     Provincie,

     Gemeente

FROM

(ooxml, embedded labels, table is Tabel1);

Tussentabel2:

LOAD Land,

     Provincie,

     Stad

FROM

(ooxml, embedded labels, table is Tabel2);

Samenvoeging:

Load *

RESIDENT Tussentabel1;

Join

Load *

Resident Tussentabel2;

Drop Table Tussentabel1;

Drop Table Tussentabel2;

But afterwards he dropped both tables, but the new table "Samenvoeging" is not created.... What am i doing wrong?

It Looks so simple.....

Re: Simple join of two resident tables

sdmech81 — Tue, 31 Jan 2017 11:42:14 GMT

HI,

Please send the excel data.

Re: Simple join of two resident tables

Tue, 31 Jan 2017 11:43:43 GMT

Hi Frido

Try This

Tussentabel1:

LOAD Land,

Provincie,

Gemeente

FROM

(ooxml, embedded labels, table is Tabel1);

Join(Tussentabel1)

LOAD Land,

Provincie,

Stad

FROM

(ooxml, embedded labels, table is Tabel2);

Re: Simple join of two resident tables

sdmech81 — Tue, 31 Jan 2017 11:43:52 GMT

I think problem is tht you have 8 similar filed and join is happening based on all 8 filed.

Identify the filed based on which u want to join and alias rest of the fieleds..

Re: Simple join of two resident tables

vinieme12 — Tue, 31 Jan 2017 11:45:28 GMT

because your fieldnames are the same Qlikview will auto concatenate both tables, just try as below

Tussentabel1:

LOAD Land,

Provincie,

Gemeente

FROM

(ooxml, embedded labels, table is Tabel1);

Join

Tussentabel2:

LOAD Land,

Provincie,

Stad

FROM

(ooxml, embedded labels, table is Tabel2);

Re: Simple join of two resident tables

Tue, 31 Jan 2017 11:45:44 GMT

Otherwise use this

Tussentabel1:

LOAD Land,

Provincie,

Gemeente

FROM

(ooxml, embedded labels, table is Tabel1);

Tussentabel2:

LOAD Land,

Provincie,

Stad

FROM

(ooxml, embedded labels, table is Tabel2);

Samenvoeging:

Noconcatenate Load *

RESIDENT Tussentabel1;

Join (Samenvoeging)

Load *

Resident Tussentabel2;

Drop Table Tussentabel1;

Drop Table Tussentabel2;

The reason is your Samenvoeging will concatenate with Tussentabel1. (Because all the field names are the same)

Kind Regards

Re: Simple join of two resident tables

sdmech81 — Tue, 31 Jan 2017 12:13:53 GMT

Yes but why do want to join them after loading you can directly join them while loading and store them.

As told by vinith,ur filed names are same it will auto concatenate.

Still u want to do say no concatenate between above tables then do.

Re: Simple join of two resident tables

buzzy996 — Tue, 31 Jan 2017 15:11:41 GMT

just add "Noconcatenate" keyword before start of ur samenvoeging table load script and see?

Re: Simple join of two resident tables

intervigilium — Wed, 22 Feb 2017 11:58:18 GMT

Hi all,

Thanks for all help! your input helped a lot!

Sorry for the slow response. Direct after implementing the solution some not foreseen events happened, so the feedback took some time.

Greetings!