Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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;
try it out
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
Liron,
Works perfectly thankyou.