Join or Concatenate!?

[tiklabaq]

Hi Friends,

I am sitting on the computer this morning and therefore I think my brain is not working as it should.

I have two tables, once header data and once position data and some info data.

Looks like this:

Header:

Order	Material	Quantity	Date
Ord123	Mat1	100	01.09.2017

Position:

Order	Workcenter	Date	hours
Ord123	W1	31.08.2017	1
Ord123	W2	30.08.2017	2

Mat_Data:

Material	Group
Mat1	GroupA

Wrkc_Data:

Workcenter	Name
W1	NameW1
W2	NameW2

The Result should be:

Order	Material	Group	Workcenter	Name	Date	Quantity	hours
Ord123	Mat1	GroupA	W1	NameW1	01.09.2017	100	-
Ord123	Mat1	GroupA	W2	NameW2	01.09.2017	100	-
Ord123	Mat1	GroupA	W1	NameW1	31.08.2017	-	1
Ord123	Mat1	GroupA	W2	NameW2	30.08.2017	-	2

You can see in the Result Table that the Quantity is now in both Workcenter, this is correct. But in the Chart I want to see only the Quantity 100 if I select both Workcenter. The sum of Quantity for this order should always be the Quantity in the header, however how much Workcenters are in the Position.

But for the hours it's not the same, here I want to see in the chart the sum of all Workcenters, in this example 3.

If I make a Join, then the Quantity is wrong in the chart. If I make it with Concatenate then for the Quantity the Workcenter is missing.

hope I could explain it well, as I said my head already bursts.

I thank you in advance.

[tiklabaq · Accepted solution]

Hi Eduardo,

yes I did. I said before, that I tried it in the way like Andrew wrote.

But the problem was, how to join with a mastercalender.

At the end, I create a second mastercalender and make a new name for the date. (HeaderDate)

In the chart with Quantity I have the second calender, in the chart with hours the first calender.

And if I select orders or workcenters in both charts I see the right results. This is the perfect way for me.