Need To Merge Rows and Create New Table

[pnn44794]

I have an Excel file that I need to load into Qlikview where I need to take the original table and create a new table while also retaining the original table by doing the following and load both into the dashboard:

1. Roll up (Sum??) Budget_Cost and Current_Cost to the estimate id level
2. Merge rows where estimate_item_id from the current row matches the previous row or vice-versa (Peek???) with the idea to get Budget_Cost and Current_Cost on one row - For example, rows 4 - 9 on the Original-Table tab should be three rows in the attached sample data for the New-Table-Needed with one row having -748800 for both Budget and Current_Costs, one with -734400 for both and finally one with -662400 for both
3. A project_id can have multiple estimate id's and estimate id's can have multiple estimate_item_id's
4. I need to be able to tie the original table to the new table as the original table will have the detail behind the new table
5. The original table will reduce to 72 rows from 182

I've attached a spreadsheet with three tabs.  The first tab is the Original Data.  The second is the new table I need and the third tab is a sample pivot table I need to build in Qlikview.

[pnn44794]

@sunny_talwar 

This might be up your alley with your experience and knowledge.

[pnn44794 · Accepted solution]

I was able to solve the problem with set analysis without having to merge any rows.

1.) Sum({<estimate_id = {"*"}, estimate_item_id = {"*"}>} Budget_Cost) //Labeled expression Budget Cost

2.) Sum({<estimate_id = {"*"}, estimate_item_id = {"*"}>} Current_Cost)  //Labeled expression Current Cost

And then

3.) ([Budget Cost] - [Current Cost])