Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
Vamsi_T
Contributor
Contributor

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

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

 

Labels (2)
1 Solution

Accepted Solutions
sunny_talwar

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;

image.png

View solution in original post

23 Replies
sunny_talwar

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

Vamsi_T
Contributor
Contributor
Author

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
Contributor
Contributor
Author

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

sunny_talwar

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;

image.png

Vamsi_T
Contributor
Contributor
Author

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

sunny_talwar

Sounds good.

Vamsi_T
Contributor
Contributor
Author

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;