Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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;
You can apply group by on Table 2 to get the consolidated values for each ID. You can then left join it with table1.
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;
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
Thanks all for the helpful answers!
I will try them all and have a look at the results.