Joining Tables in Qlikview

[afbraga1]

Hello,

So I have this case where I am trying to do Join but I am not getting the information as I expected. On the image below I have first a TableBox(1st) and a Chart(2nd) of my examples.

I am doing JOINS and I expected to see in the where Shift=2, the Team=Team A and also a new line with Team C, but I can't seem to do it.If change the order of the Tables being joined it starts showing a different Team, but stops showing the other two.

I had the idea that the JOIN order was arbitrary. I know I can solve this example removing the JOINS, though I want to know if it is possible with JOINS, because even though this is a simple and small case, it is part of a big one with many more tables, where JOINS are present. At the moment, I am getting only parts of data depending on the order of the tables being joined. If someone could explain me understand how to do this it would be most appreciated.

Best regards,

Andre Braga

EXAMPLE CODE:

Table1:

LOAD * INLINE [

    %UptimeKey,Plant_COD, ProductionLine_COD, Date, Shift

    1, P222, 202, 05-09-2016, 2

    2, P222, 202, 05-09-2016, 3

];

JOIN

LOAD * INLINE [

Plant_COD, ProductionLine_COD, Date, Shift, Team

P222, 202, 05-09-2016, 3, Team B

];

JOIN

LOAD * INLINE [

Plant_COD, ProductionLine_COD, Date, Shift, Team

P222, 202, 05-09-2016, 2, Team A

P222, 202, 05-09-2016, 3, Team B

];

JOIN

LOAD * INLINE [

Plant_COD, ProductionLine_COD, Date, Shift, Team

P222, 202, 05-09-2016, 2, Team C

];

MetricsUptime:

LOAD * INLINE [

   %UptimeKey,Key, #UptimeHours

   1, 2, 8

   2, 4, 8

];

[swuehl]

Maybe like this?

Table1:

LOAD * INLINE [

    %UptimeKey,Plant_COD, ProductionLine_COD, Date, Shift

    1, P222, 202, 05-09-2016, 2

    2, P222, 202, 05-09-2016, 3

];

TMP:

LOAD * INLINE [

Plant_COD, ProductionLine_COD, Date, Shift, Team

P222, 202, 05-09-2016, 3, Team B

];

CONCATENATE (TMP)

LOAD * INLINE [

Plant_COD, ProductionLine_COD, Date, Shift, Team

P222, 202, 05-09-2016, 2, Team A

P222, 202, 05-09-2016, 3, Team B

];

CONCATENATE (TMP)

LOAD * INLINE [

Plant_COD, ProductionLine_COD, Date, Shift, Team

P222, 202, 05-09-2016, 2, Team C

];

JOIN (Table1)

LOAD * RESIDENT TMP;

DROP TABLE TMP;

MetricsUptime:

LOAD * INLINE [

   %UptimeKey,Key, #UptimeHours

   1, 2, 8

   2, 4, 8

];

[swuehl]

As soon a you join the first table containing Team to Table1, it will be part of the key fields for the following tables containing also Team field. Team field key values are non-matching, hence this will not lead to your requested result.

[swuehl]

This may also work:

Teams:

LOAD * INLINE [

Plant_COD, ProductionLine_COD, Date, Shift, Team

P222, 202, 05-09-2016, 3, Team B

];

Concatenate (Teams)

LOAD * INLINE [

Plant_COD, ProductionLine_COD, Date, Shift, Team

P222, 202, 05-09-2016, 2, Team A

P222, 202, 05-09-2016, 3, Team B

];

Concatenate (Teams)

LOAD * INLINE [

Plant_COD, ProductionLine_COD, Date, Shift, Team

P222, 202, 05-09-2016, 2, Team C

];

Table1:

JOIN (Teams)

LOAD * INLINE [

    %UptimeKey,Plant_COD, ProductionLine_COD, Date, Shift

    1, P222, 202, 05-09-2016, 2

    2, P222, 202, 05-09-2016, 3

];

MetricsUptime:

LOAD * INLINE [

   %UptimeKey,Key, #UptimeHours

   1, 2, 8

   2, 4, 8

];

Or if you don't like concatenation:

Teams:

LOAD * INLINE [

Plant_COD, ProductionLine_COD, Date, Shift, Team

P222, 202, 05-09-2016, 3, Team B

];

JOIN

LOAD * INLINE [

Plant_COD, ProductionLine_COD, Date, Shift, Team

P222, 202, 05-09-2016, 2, Team A

P222, 202, 05-09-2016, 3, Team B

];

JOIN

LOAD * INLINE [

Plant_COD, ProductionLine_COD, Date, Shift, Team

P222, 202, 05-09-2016, 2, Team C

];

Table1:

JOIN (Teams)

LOAD * INLINE [

    %UptimeKey,Plant_COD, ProductionLine_COD, Date, Shift

    1, P222, 202, 05-09-2016, 2

    2, P222, 202, 05-09-2016, 3

];

MetricsUptime:

LOAD * INLINE [

   %UptimeKey,Key, #UptimeHours

   1, 2, 8

   2, 4, 8

];