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.

[Anil_Babu_Samineni]

Try to do inner join for all tables to make one table and loaf fields into table box then show the image. I don't have software right now

[tiklabaq]

Thank you for your answer. But inner join is not the solution.

I don't have only a Table, there are many Charts, for example a Bar Chart in the report.

So I have to show the right quantity and hours in this charts.

[eduardo_dimperio]

Hi Ibrahim,

To me the best method in order are: Keep, Join, Concatenate.

Keep: like name says will keep all tables, reduce the data and will look like a Join

Join: Use Left or Right Join, Inner could result in a cartesian with a big and  useless process time

Concatenate: It join all results and could bring a lot of duplicated values.

So in your case use a Left Keep or a Left Join to Header, Position and Wrkc_Data, but like you dont have common field in Math data( this not good cause your join will result in a catersian) use a Concatenate.

This article could help you

Understanding Join, Keep and Concatenate

[tiklabaq]

Thank you Eduardo.

I tried it with Left Keep already, but the problem is, with Left Keep you don't have one table, the result are two tables.

So it's not possible to make a Resident Tabel with this data or a join of other tables that should be joined on both tables.

It's not only difficult to solve it, it's even difficult to explain it

Thanks anyway

[eduardo_dimperio]

I understand very well, i already had this kind of problem.

Do this:

Header:

Load

*

FROM 'Header Place';

Left Join (Header)

Load

*

FROM 'Position Place';

Left Join (Header)

Load

*

FROM 'Wrkc_Data Place';

//Now you have 1 table 'Header'

Concatenate (Header)

Load

*

FROM 'Mat_Data Place';

//I think this could souve your problem

[Anil_Babu_Samineni]

So, in bar chart you need P() and E() for other tables data. Could you provide screenshot or QVW to look where we need to do

[effinty2112]

Hi Ibrahim,

Maybe:

Header:

LOAD * INLINE [

    Order, Material, Quantity, Date

    Ord123, Mat1, 100, 01.09.2017

];

Left Join

LOAD * INLINE [

     Material, Group

    Mat1, GroupA

];

Position:

LOAD * INLINE [

    Order, Workcenter, PosDate, hours

    Ord123, W1, 31.08.2017, 1

    Ord123, W2, 30.08.2017, 2

];

Left Join

LOAD * INLINE [

    Workcenter, Name

    W1, NameW1

    W2, NameW2

];

Gives two tables in the data model and this tablebox:

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

Regards

Andrew

[tiklabaq]

Hi Andrew,

thank you. This way I have tried too, but how can I now join the two different dates with a mastercalender?

[eduardo_dimperio]

Hi Ibrahim,

Did you solve your problem?