New sum in load script dilema

[#1]

Hi, im still having a headach with using 'sum' in the load script.  I am trying to generare the table below: -

Ref	no	shouldbe
1	1	14
1	4	14
1	5	14
2	1	4
2	1	4
2	1	4
1	1	14
1	1	14
2	1	4
3	1	3
3	1	3
3	1	3
1	1	14
1	1	14

I am trying to do the sum of 'no' grouped by Ref to produce and additional column that matches 'shouldbe'.

The code I am using is: -

Main:
LOAD Ref,
     [no],
     shouldbe
FROM
data.xlsx
(ooxml, embedded labels, table is Sheet1);

Main2:
LOAD Ref as ref2,
  [no],
  sum([no]) as sum,
     shouldbe

RESIDENT Main
GROUP BY Ref , [no], shouldbe ;
DROP TABLE Main;

However the table that is being produced looks like this :-

ref2	no	sum	shouldbe
3	1	3	3
1	4	4	14
2	1	4	4
1	1	5	14
1	5	5	14

I only want the load to produce one 'ref 1' with the sum being 14.

I'm baffled, any help would be greatly received.

Many thanks

[lironbaram · Accepted solution]

hei your Script should look like this

Main:
LOAD Ref,
[no],
shouldbe
FROM
data.xlsx
(ooxml, embedded labels, table is Sheet1);

left join(Main)

LOAD Ref,
sum([no]) as sum  
RESIDENT Main
GROUP BY Ref;

[SunilChauhan]

try it out

[kaushiknsolanki]

Hi,

     Try this.

Data:
LOAD * INLINE [
  Ref, no, shouldbe
1, 1, 14
1, 4, 14
1, 5, 14
2, 1, 4
2, 1, 4
2, 1, 4
1, 1, 14
1, 1, 14
2, 1, 4
3, 1, 3
3, 1, 3
3, 1, 3
1, 1, 14
1, 1, 14
];

Data1:
Load Ref as REF,
no as NO,
Sum(no) as Count,
max(shouldbe) as max
Resident Data group by Ref,no;

Regards,

Kaushik Solanki

[#5]

Liron,

Works perfectly thankyou.