How to consolidate two or more rows into one

[Suyash]

Hi everyone, 

I hope you're well. 

Basically my question was regarding how if you have let's say two or more rows in an Excel file with the same value for a particular field, let's say it's the same City for example, and you'd like to essentially consolidate all of the information shown for that city into just one row for example the volume of rainfall it receives on a particular day, how would you go about doing that? 

So just to illustrate: 

CityName      |           Day            |                Volume of rainfall

Seattle                 02/08/2020                             340

Madrid                02/08/2020                              60

Seattle                03/08/2020                              120

So essentially, in the above, how would I design it in Qlik so that for Seattle, there would only be one row and the volume of rainfall would be consolidated to 460? 

Thanks a lot for reading!

[Taoufiq_Zarra]

@Suyash  Like ?

Data:

load * inline [
CityName,Day,Volume of rainfall
Seattle,02/08/2020,340
Madrid,02/08/2020,60
Seattle,03/08/2020,120

];
Final:

load CityName, sum([Volume of rainfall]) as [Volume of rainfall] resident Data group by CityName;

drop table Data;

output:

[Suyash]

Hi Taoufiq, 

Thank you so much for your reply 🙂 

I haven't tried your solution yet as what I'm trying to achieve is a modified version of what you've done essentially with a preceding load, so like: 

Final: 

load sum([Volume of rainfall]) as [Volume of rainfall],*;

load CityName, [Volume of rainfall] from Excel file; 

Please excuse the slight pseudocode used above! 

Thanks a lot once again.