Insert a new row with sum of two rows in Load script

[Vamsi_T]

Hello All.

I would like to insert a new row ( sum of below rows) for every four rows in the data load script. Is this possible?

Kindly share the working solution...

Regards,

Vamsi

[sunny_talwar]

Would you be able to share your sample data in an Excel file?

[Vamsi_T]

Yes. I have attached it.

[sunny_talwar]

One quick question, is your data sorted like this within your datasource? Because if it isn't, then it will be challenge to know which Sum belongs to which Brand (As, Bs, Cs etc)

[Vamsi_T]

Yes, it sorted with the same structure after loading the data.

[sunny_talwar · Accepted solution]

Something like this?

Table:
LOAD If(Mod(RowNo(), 4) = 0, 4, Mod(RowNo(), 4)) as Group,
	 Ceil(RowNo()/4) as Grouping,
	 Brand, 
     Area, 
     Sales
FROM
[..\..\Downloads\Test (1).xlsx]
(ooxml, embedded labels, table is Sheet1);

Concatenate (Table)
LOAD 0 as Group,
	 Grouping,
	 'Total (Sum+' & Only(If(Group = '2', Brand)) & ')' as Brand,
	 Only(Area) as Area,
	 Sum(Sales) as Sales
Resident Table
Where Group <= 2
Group By Grouping;

[Vamsi_T]

Yes, It works. I have to try with the main data. Thank you for the solution.

[sunny_talwar]

Sounds good.

[Vamsi_T]

I have one more question. Can I group the rows based on Area and Date?

I have added dates also in the attached document.

[sunny_talwar]

I don't see why not

Table:
LOAD If(Mod(RowNo(), 4) = 0, 4, Mod(RowNo(), 4)) as Group,
	 Ceil(RowNo()/4) as Grouping,
	 Date,
	 Brand, 
     Area, 
     Sales
FROM
[..\..\Downloads\Test (2).xlsx]
(ooxml, embedded labels, table is Sheet1);

Concatenate (Table)
LOAD 0 as Group,
	 Grouping,
	 Date,
	 'Total (Sum+' & Only(If(Group = '2', Brand)) & ')' as Brand,
	 Only(Area) as Area,
	 Sum(Sales) as Sales
Resident Table
Where Group <= 2
Group By Grouping, Date;