Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Combining two tables

Dears,

I have an exel file with different tables

Table 1 with field 1.2.3.4

Table 2 with field 1.2.5.6.7

I want to create table 3 with fields 1.2.3.4.5.6.7

where 5. is a new field

where 1.2. is fed from both tables, and where relevant completed with 4. 6. or 7.

but somehow it doesn't work and I don't see why not. Can somebody help?

I created in Qlik Sense

Table 1

Load distinct

1 as field 1,

2 as field 2,

3 as field 3,

4 as field 4

from abc

Table 2

Load distinct

1 as field 1a,

2 as field 2a,

5 as field 5,

6 as field 6,

7 as field 7

from def

this because I could't create two tables with same fieldname

Then I created a new section with

Table 3

Load *,

field 1 as field 1b,

field 2 as field 2b,

3 as field 3,

4 as field 4

resident Table 1;

concatenate (Table 3):

Load *,

field 1a as field 1b,

field 2a as field  1b

5 as field 5,

6 as field 6,

7 as field 7

resident Table 2;

drop table 1;

drop table2;

I don't know what I do wrong, but it does concatenate, but does not drop the tables giving me an end result combining all fields.

Can you help me in the structure of my script?

thanks a lot,

3 Replies
agigliotti
Honored Contributor II

Re: Combining two tables

maybe:

drop tables [Table 1], [Table 2]  ?

jean-philippe
New Contributor II

Re: Combining two tables

Hi,

You must create a Link Table with key field (Field 1 - Field 2 and Field 1a - Field 2a).

Link Table:

Load Distinct

     Field 1 as Field 1b,

     Field 2 as Field 2b

From Table 1;

Concatenate(Link Table)

Load Distinct

     Field 1a as Field 1b,

     Field 2a as Field 2b

From Table 2;

Then you left join (Link Table) From Table 1 et Table 2

Left join (Link Table)

Load *,

     Field 1 as Field 1b,

     Field 2 as Field 2b

From Table 1;

Left join (Link Table)

Load *,

     Field 1a as Field 1b,

     Field 2a as Field 2b

From Table 2;

Drop Table Table 1, Table 2;

sasiparupudi1
Honored Contributor III

Re: Combining two tables

A solution would be

Abc:
Load
F1&'|'&F2 as Key1,
F3,
F4;
Load * inline
[
F1,F2,F3,F4
];

Def:
Load
F1&'|'&F2 as Key1,
F1,
F2,
F5,
F6,
F7;
Load * inline
[
F1,F2,F5,F6,F7
];

Left Join(Abc)
Load Key1,
F1,
F2,
F5,
F6,
F7
Resident Def;

Drop Table Def;
drop Field Key1;

Community Browser