Merge two tables into one

[reporting_neu]

Hello, everyone,

I have two tables with dependent information.

In one table there are parts with more than one information and in the other table as well. I have to put these together in dependency. But how?

Table1:

RMNr	Prozess	Position	CostItemGroupID	Target time	CostItemGroup	Date	Time
1	1000	1	110	5,0	production	27.09.2021	08:00
1	1000	2	120	2	shipping	28.09.2021	10:00
2	1022	1	110	1,3	production	25.09.2021	07:20
2	1022	1	120	2,8	shipping	28.09.2021	08:00
3	1000	1	110	5,0	production	29.09.2021	08:30

Table2: (Master)

Part	Status	Deadline	Clerk	Target quantity	Desired date	StatusID	WorkOrderType
1000	in production	30.09.2021	HP	100	05.10.2021	1	production
1000	planned	30.09.2021	HP	100	05.10.2021	2	production
1022	in production	30.09.2021	HP	75	05.10.2021	1	production
1022	instructed	30.09.2021	HP	75	05.10.2021	3	production
1000	in production	30.09.2021	HP	36	05.10.2021	1	refurbish

I have to assign the process from Table 1 to each part from Table 2. The part number and process number are identical. However, the process number can appear several times in table 1, just like the part number in table 2.

Several processes must therefore be assigned to a part (from Table 2 from Table 1).
Or the other way around, each process has several parts (from Table 1 to Table 2).

When I use a mapping, I can only assign one value at a time, but I don't get multiple dimensions.

Do you have any advice for me?

[Qlik1_User1 · Accepted solution]

@reporting_neu Try below

Master:

LOAD * Resident Table2;

LEFT JOIN(Master)

LOAD * Resident Table1;

DROP Tables Table1, Table2;

By above approach you have single table with all data.

[reporting_neu]

Many thanks for your response.

Unfortunately, when I use the join, I get all the characteristics for each part.

NoConcatenate
Master:
Load * Resident Table2;
Left join (Master)
Load * Resident Table1;
Drop Tables Table2, Table1;

A "RMNr" occurs a maximum of three times in the data record. After the join, it is available 1000 times. Each individual expression is then available for each individual data record. 😔

[Qlik1_User1]

Drop all non required fields from master table, If that does work use INNER JOIN

NoConcatenate
Master:
Load * Resident Table2;
INNER join (Master)
Load * Resident Table1;
Drop Tables Table2, Table1;

and drop all non required fields.

[abhijitnalekar]

Hi,

It looks very difficult to join the above two tables with Process and Part fields.

We need to create a composite key to join these two tables.

[reporting_neu]

Unfortunately, it doesn't work with that either.

Don't I have to somehow link the data fields, like with SQL?

For example: Left Join Table1 ON Part = process

[reporting_neu]

I feared that. But the key should be identical for both. But there is only the part and process that match.

[Qlik1_User1 · Accepted solution]

@reporting_neu 

For example: Left Join Table1 ON Part = process --> We did this only, Qlik automatically creates a join based on matching fields name so Please change/rename the field name Part to Process in table 2 or Process to Part in table1.
Note - Qlik is case sensitive, so name needs to be exactly same.
After that this , join will work.

[abhijitnalekar]

Hi @reporting_neu ,

Have you tried conactination? 

[Qlik1_User1]

@reporting_neu 
For example: Left Join Table1 ON Part = process --> We did this only, Qlik automatically creates a join based on matching fields name so Please change/rename the field name Part to Process in table 2 or Process to Part in table1.
Note - Qlik is case sensitive, so name needs to be exactly same.
After that this , join will work.

Master:
LOAD
Part,
Status,
Deadline,
Clerk,
Target,
quantity,
Desired,
date,
StatusID,
WorkOrderType
Resident Table2;

LEFT JOIN(Master)

LOAD
RMNr,
Prozess as Part,
Position,
CostItemGroupID,
Target time,
CostItemGroup,
Date,
Time
Resident Table1;

DROP Tables Table1, Table2;