Qlikview SUM with a one to many relationship

[#1]

Hi

I have two database tables that are in a one to many relationship to each other.

The ID field link the two tables together.

Table1:

ID, Qty

1,100

2,150

3,200

4,250

Table2:

ID, AdditionalQty

1,10

1,20

1,30

4,20

4,40

I want to load Table1 so that there will be a new field in Table1 that is a total of Qty + AdditionalQty and will look like this

after the LOAD.

Table1:

ID, Qty, Total

1,100,160

2,150,150

3,200,200

4,250,310

Thanks in advance.

[Anil_Babu_Samineni]

You may try like below

Create one straight table with the following

Dimension

ID

Expressions are

1) Sum(Qty)

2) Sum(Qty) + Sum(TOTAL <ID> AdditionalQty)

Output Seems like image

PFA for Reference.

[MindaugasBacius]

Try like this:

Table:

Load *

From Source;

Left Join (Table)

Load ID

    ,sum(AdditionalQty) as Sum

From Source

Group by ID;

tmp:

Load ID

    ,Qty

    ,Qty + Sum as Total

Resident Table;

Drop table Table;

[saimahasan]

You can apply group by on Table 2 to get the consolidated values for each ID. You can then left join it with table1.

[maxgro]

Table1:

load * inline [

ID, Qty

1,100

2,150

3,200

4,250

];

left join (Table1)

load

  ID,

  sum(AdditionalQty) as AdditionalQty

Group by ID;

//Table2:

load * inline [

ID, AdditionalQty

1,10

1,20

1,30

4,20

4,40

];

Table2:

LOAD

  *,

  RangeSum(Qty, AdditionalQty) as TotalQty

Resident Table1;

DROP Table Table1;

[its_anandrjs]

Try to load the table this ways

Table1:
LOAD * Inline
[
ID, Qty
1,100
2,150
3,200
4,250
];

Table2:
LOAD * Inline
[
ID, AdditionalQty
1,10
1,20
1,30
4,20
4,40
];

 
Left Join(Table1)
LOAD ID,Sum(AdditionalQty) as AdditionalQty Resident Table2
Group By ID;
DROP Table Table2;

Final:
LOAD
ID,
Sum(Qty) as Qty,
Sum(Qty)+Sum(AdditionalQty) as Total
Resident Table1
Group by ID;

DROP Table Table1;

Regards

Anand

[#7]

Thanks all for the helpful answers!

I will try them all and have a look at the results.