topic Joining 2 tables without duplicating data in QlikView

Joining 2 tables without duplicating data

Tue, 23 Oct 2012 15:47:26 GMT

Hi all,

I have a problem, seems not so hard but I am totally stuck since hours.

Hope some will find a solution !

I have 2 tables :

Main table :

Order Code	Project Code	Line	Amount
1	34	10	300
2	37	10	500
3	39	40	200
3	39	70	350
3	39	90	1000
3	40	110	45
4	39	20	240

Has you can see, I can have x project for each order and x order for each project !

I want to join this table with a second table :

Project Code	Budget
34	200
37	600
39	400
40	150

What I need, is to have only one budget for each couple Project in one order :

Order Code	Project Code	Line	Amount	Budget
1	34	10	300	200
2	37	10	500	600
3	39	40	200	400
3	39	70	350	-
3	39	90	1000	-
3	40	110	45	150
4	39	20	240	400

As anyone any idea ?

Thx for help!

Re: Joining 2 tables without duplicating data

Tue, 23 Oct 2012 16:46:34 GMT

If this is Really what you want to do then you can do it like this:

Main:
LOAD * INLINE [
OrderCode,ProjectCode,Line,Amount
1, 34, 10, 300
2, 37, 10, 500
3, 39, 40, 200
3, 39, 70, 350
3, 39, 90, 1000
3, 40, 110, 45
4, 39, 20, 240];

LEFT JOIN(Main) LOAD * INLINE [
    ProjectCode, Budget
    34, 200
    37, 600
    39, 400
    40, 150 ];

NewMain:
LOAD OrderCode
,ProjectCode
,Line,Amount
,IF(ProjectCode=Previous(ProjectCode),0,Budget) AS NewBudget
RESIDENT Main;

DROP TABLE Main;

HOWEVER

I would do a normal join on the table or keep them as seperate tables, the reality is that if you do the above then certain views of the data are not going to show you what you want to see, if for any reason you have filtered done to line 70 or 90 then the chart will show you that there is zero budget for project 39 and this is not the case.

As a default QlikView will show you 400 against each line in a detailed view of the data, this is also correct because that project has a budget of 400, the only thing that would be incorrect in this case is the fact that the total is not right, but I think there are workarounds for this.

So, be careful with what you decide to do in this case.

Re: Joining 2 tables without duplicating data

cesaraccardi — Tue, 23 Oct 2012 17:02:30 GMT

Hi,

I also think you can keep the tables separated or if you really want to join, the values will be duplicated and you can handle in the expression by using advanced aggregations (aggr).

Regards,

Cesar Accardi

Re: Joining 2 tables without duplicating data

rwunderlich — Tue, 23 Oct 2012 17:42:51 GMT

I would keep them as seperate tables. If you had a modeling reason to put them together in a single table, I would consider concatenate instead of join. See the example at the bottom of this post for a similar situation.

http://qlikviewnotes.blogspot.com/2009/11/understanding-join-and-concatenate.html

-Rob

Re: Joining 2 tables without duplicating data

Tue, 23 Oct 2012 19:17:28 GMT

Thx for your responses,

You are all right, the best way is to keep tables seperated.

I will explain all the situation :

If project code is begining with an 8 I have to use a specific table, if it begins with a 4 another ... so I wrote the following script :

ORDER_8_Spec:

LOAD OrderCode,ProjectCode,Line,Amount

From ORDER.qvd (qvd)

Where Left([ProjectCode],1)=8;

Left Join

LOAD

CPRJ as [ProjectCode],

BUD as Budget,

RF as Reforecast

FROM

T1.qvd

(qvd);

ORDER_4_Std:

LOAD OrderCode,ProjectCode,Line,Amount

From ORDER.qvd (qvd)

Where Left([ProjectCode],1)=4;

Left Join

LOAD

CPRJ as [ProjectCode],

BUD as Budget,

RF as Reforecast

FROM

T2.qvd

(qvd);

ORDER_359:

LOAD *

From ORDER.qvd (qvd)

Where Left([ProjectCode],1)=3 or Left([ProjectCode],1)=5 or Left([ProjectCode],1)=9;

Left Join

LOAD

CPRJ as [ProjectCode],

BUD as Budget,

RF as Reforecast

FROM

T3.qvd

(qvd);

//ORDER CONCATENATE

ORDER:

LOAD OrderCode,ProjectCode,Line,Amount

Resident ORDER_8_Spec;

Concatenate

LOAD *

Resident ORDER_4_Std;

Concatenate

LOAD *

Resident ORDER_359;

I thought that this solution should work but:

when I display a simple table (dim project, expr sum(budget))

i have a sum of all lines of the project but the project has only one budget !

Is it clear ?

Many thanks for your help !

Re: Joining 2 tables without duplicating data

cesaraccardi — Tue, 23 Oct 2012 20:37:10 GMT

Hi,

You don't need to do the concatenation part, because the fields in the previous loadings have the same name and they will be concatenated automatically. If the value is displayed multiple times you can use an expression like this: sum(aggr(avg(Budget),Project)). This way you calculate the average by budget (the value of the line), and finally you sum to get the totals without duplicated values.

Re: Joining 2 tables without duplicating data

Mon, 05 Nov 2012 09:43:49 GMT

Hi,

Your solution is working!

What a shame that the solution is not in the script but in the design.

For me, the aggr solution is easy and fast but if the end-user try to build a table he will not understand the expression.

Thanks for your help !