table completed with data form different table

[TMK]

Hello,

I started to work with Qlik Sense for some month ago. The last days I have worked on following issue and could not fix it.

This table A is a list with the produced parts:

Date_Production	Part_ID	Colour_ID
07.01.2020	932580	300
07.01.2020	932581	300
07.01.2020	932582	475
08.01.2020	932971	300
08.01.2020	932972	668
09.01.2020	933357	475
09.01.2020	933358	300

And the following table is the table B with the applied robots. The Date_Robot indicates the start of the production with the robot and the Colour ID “All” includes all colours except “300”.

Date_Robot	Colour_ID	Robot_ID
01.01.2020	All	12
01.01.2020	300	34
08.01.2020	All	34
08.01.2020	300	12

At the end the table should look like this:

Date_Production	Part_ID	Colour_ID	Robot_ID
07.01.2020	932580	300	34
07.01.2020	932581	300	34
07.01.2020	932582	475	12
08.01.2020	932971	300	12
08.01.2020	932972	668	34
09.01.2020	933357	475	34
09.01.2020	933358	300	12

I have tried it already with “OUTER JOIN” combined witch “VARIABLE” in “DO-WHILE-LOOP”, but failed catastrophically. Have anybody an idea, how I should go on?

Regards,

TMK

[pedrobergo]

Hi @TMK ,

I prepare a load script to you, 

1. You need create a Temp table with only the 'ALL' Colour ID and crossjoin the TableA with it.
2. Then you create a Final Table with the existing colours id at both tables A and B and join TableA with it.
3. Sou you must to union the Temp Table with the Final Table
4. DROP other tables to avoid synthetic keys

See the app script.

Good luck,

Pedro

[Taoufiq_Zarra]

what is the correlation between the two dates Date_Production and Date_Robot ?

[mueller-kambeitz]

Hi  Peter,

Thank you very much for your help. I tried it already out, but unfortunately is this the result:

Date_Production	Part_ID	Colour_ID	Robot_ID
07.01.2020	932580	300	12
07.01.2020	932580	300	34
07.01.2020	932581	300	12
07.01.2020	932581	300	34
07.01.2020	932582	475	12
07.01.2020	932582	475	34
08.01.2020	932971	300	12
08.01.2020	932971	300	34
08.01.2020	932972	668	12
08.01.2020	932972	668	34
09.01.2020	933357	475	12
09.01.2020	933357	475	34
09.01.2020	933358	300	12
09.01.2020	933358	300	34

At this table each part has two different Robot_ID’s. But each part with its unique Part_ID should have only one Robot_ID.

I will try to use your load script and I hopefully can rework it to get finally the needed result. It would be great, if you would have a suggestion for the rework.

Kind regards,

Tobi

!!!Sorry for the confusion. Foolishly I have two logins (one for work and one for private) and mixed them up. I am TMK and Tobi!!!

[TMK]

Hi @Taoufiq_ZARRA ,

The Date_Production is the day of the production of the parts.

The Date_Robot is the day where a new setup of the robots were installed for the colours.

For example:

From 01/01/2020 till 01/07/2020 was the robot with Robot_ID 34 used for all parts with Colour_ID 300. With the begining of 01/08/2020 was the Robot_ID 12 used for part with Colour_ID 300.

Regards,

Tobi

[TMK]

The Date_Production is the day of the production of the parts.

The Date_Robot is the day where a new setup of the robots were installed for the colours.

For example:

From 01/01/2020 till 01/07/2020 was the robot with Robot_ID 34 used for all parts with Colour_ID 300. With the begining of 01/08/2020 was the Robot_ID 12 used for part with Colour_ID 300.

Regards,

Tobi

[TMK · Accepted solution]

Hi Pedro,

thank you very much for your help and your efforts. As I wrote in an reply before your suggestion is unfortunately not working in the way I need.

The last weeks I tried out different ideas. Finally I got it. The best way I found is to use "intervalmatch". See the app script.

Regards,

Tobi