topic Re: Connecting two tables with 3 keys. in QlikView

Connecting two tables with 3 keys.

Anonymous — Fri, 11 Dec 2015 14:51:57 GMT

Good day!

Colleagues, how to connect correctly two tables with 3 the same fields?:

Source1:

LOAD WareHouse,

Nomenclature,

Date,

Volume

FROM

[Test.xlsx]

(ooxml, embedded labels, table is Source1);

Source2:

LOAD WareHouse,

Nomenclature,

Date,

Volume1

FROM

[Test.xlsx]

(ooxml, embedded labels, table is Source2);

Thanks

Re: Connecting two tables with 3 keys.

settu_periasamy — Fri, 11 Dec 2015 15:22:55 GMT

How do you want the output?

Re: Connecting two tables with 3 keys.

eduardo_sommer — Fri, 11 Dec 2015 15:27:28 GMT

You can concatenate both tables, or join them.

For the first alternative, use this:

Source1:

LOAD WareHouse,

Nomenclature,

Date,

Volume

FROM

[Test.xlsx]

(ooxml, embedded labels, table is Source1);

Concatenate (Source1)

LOAD WareHouse,

Nomenclature,

Date,

Volume1

FROM

[Test.xlsx]

(ooxml, embedded labels, table is Source2);

Regards

Eduardo

Re: Connecting two tables with 3 keys.

sinanozdemir — Fri, 11 Dec 2015 15:27:38 GMT

I would probably concatenate them:

Just rename Volume1 to Volume

And your results will look like this:

Re: Connecting two tables with 3 keys.

markodonovan — Fri, 11 Dec 2015 15:30:15 GMT

Try

Creating a composite key.

This video might help.

https://youtu.be/piCSMmHbm64

Thanks

Mark

Re: Connecting two tables with 3 keys.

Fri, 11 Dec 2015 15:46:06 GMT

when you say connect, are you wanting one table with the same 3 fields but the data from all 3?

if so, you will need to use Load as ,field name> to get the field names from all tables to be the same - from the data above, Volume1 as Volume.

You do not really need to include the Concatenate statement because QlikView will do an automatic concatenation when loading tables and all of the fields are the same

Re: Connecting two tables with 3 keys.

eduardo_sommer — Fri, 11 Dec 2015 15:49:58 GMT

But, if he wants to have Volume separated from Volume1, the concatenate is needed

Re: Connecting two tables with 3 keys.

Fri, 11 Dec 2015 15:54:17 GMT

depending on the reuirement, that is a true statement and agree 100%. If there is a volume field and a volume1 field once the tables are concatenated/joined he could have an issue with nulls

Re: Connecting two tables with 3 keys.

Fri, 11 Dec 2015 16:03:46 GMT

Qlikview by default performs join but you may see synthetic table.

You can use Join or Concat to resolve the keys.

Table1:

Load

Join

Table2:

Load

Thanks,

Re: Connecting two tables with 3 keys.

Fri, 11 Dec 2015 16:07:18 GMT

the current data model as-is will be crating those synthetic keys unless a concatenate or join is done to get both tables into one

see synthetic keys being created below