topic Re: Join or Concatenate!? in QlikView

Join or Concatenate!?

tiklabaq — Tue, 22 Aug 2017 17:44:43 GMT

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.

Re: Join or Concatenate!?

Anil_Babu_Samineni — Tue, 22 Aug 2017 18:11:50 GMT

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

Re: Join or Concatenate!?

tiklabaq — Tue, 22 Aug 2017 18:18:42 GMT

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.

Re: Join or Concatenate!?

eduardo_dimperio — Tue, 22 Aug 2017 18:25:06 GMT

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

Re: Join or Concatenate!?

tiklabaq — Tue, 22 Aug 2017 18:47:05 GMT

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

Re: Join or Concatenate!?

eduardo_dimperio — Tue, 22 Aug 2017 20:31:06 GMT

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

Re: Join or Concatenate!?

Anil_Babu_Samineni — Wed, 23 Aug 2017 04:31:37 GMT

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

Re: Join or Concatenate!?

effinty2112 — Wed, 23 Aug 2017 05:59:11 GMT

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

Re: Join or Concatenate!?

tiklabaq — Wed, 23 Aug 2017 06:29:47 GMT

Hi Andrew,

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

Re: Join or Concatenate!?

eduardo_dimperio — Fri, 25 Aug 2017 13:39:04 GMT

Hi Ibrahim,

Did you solve your problem?

Re: Join or Concatenate!?

tiklabaq — Mon, 28 Aug 2017 06:14:50 GMT

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.