Data manipulation during load

[#1]

Hi there,

I need to do some data manipulation during the loading procedure and do not quite know how. I have a table such as the below

CustomerId	Sales	Aggregated_Sales	%
1	10	60	16.67%
1	20	60	33.33%
1	30	60	50.00%
2	5	13	38.46%
2	8	13	61.54%
3	21	21	100.00%

How can I calculate the Aggregated_Sales entries during the load procedure when given the CustomerId and Sales data?

Thanks a lot for your help,

Florian

[marcus_sommer]

Maybe in this way:

table1:

load CustomerId, Sales from YourTable;

     left join

Load CustomerId, sum(Sales) as Aggregated_Sales resident table1 group by CustomerId;

table2:

load *, Sales / Aggregated_Sales as % resident table1;

drop tables table1;

- Marcus

[sunny_talwar]

You don't have to do this in the script, you can come up with this on the front end if you want using this expression

Sum(TOTAL <CustomerId> Sales)

but for doing this in the script, you can do like this

Table:

LOAD CustomerId,

          Sales

FROM....

Left Join (Table)

LOAD CustomerId,

          Sum(Sales) as Aggregated_Sales

Resident Table

Group By CustomerId;

[maxgro]

you can use the total in the user interface (chart)

for aggregated sales (by customer)

Sum(total <CustomerId> Sales)

and for %

Sum(Sales) / Sum(total <CustomerId> Sales)