Load Script - Sum against reference

[#1]

Hi,

I'm looking for advice on what I feel should have a really easy solution (but for some reason I cant get it to work).

I have a table similar to the one below: -

Ref	no
1	1
1	1
1	1
2	1
2	1
2	1
1	1
1	1
2	1
3	1
3	1
3	1
1	1
1	1

What I am trying to do is sum 'No' against the 'Ref' within the load script to produce: -

Ref	no	tot
1	1	7
1	1	7
1	1	7
2	1	4
2	1	4
2	1	4
1	1	7
1	1	7
2	1	4
3	1	3
3	1	3
3	1	3
1	1	7
1	1	7

I have tried if(PREVIOUS(ref)=ref, PEEK([no]),0) + [no] as test, however this does not give the result I am after.

Can anyone advise on how I can achieve this within the load?

Many thanks

[#2]

Think I found the solution using count and grouping.

Main:

LOAD Ref,

     [no],

     tot

FROM

data.xlsx

(ooxml, embedded labels, table is Sheet1);

Main2:

LOAD Ref as ref2,

     [no],

     count([no]) as cnt,

     tot

RESIDENT Main

GROUP BY Ref , [no], tot;

DROP TABLE Main;

[johnw]

You still want all of the rows, but with the total per Ref on each row?

LEFT JOIN (MyTable)
LOAD
Ref
,count(no) as tot
RESIDENT MyTable
;

That said, I generally recommend against doing totals in the script, as they won't be sensitive to selections.  Of course, if you WANT your totals to not be sensitive to selections, the script is a good place to do that.  But otherwise, this would normally be handled in a chart.  Something like this:

dimension 1 = Ref
dimension 2 = no
expression  = count(total <Ref> no)

[#4]

Many thanks for that answer. 

The reason I am trying to do this in the load script is because I need to include the data within a pivot table against the reference; the pivot being on date and containing other information.  Using a count/sum calculation within the dimension just seemed to break the table, I think doing it this way will give a cleaner result (or at least one that will work).