How to merge two rows data in a single row in straight table

[abc_18]

Hi,

I have created a straight table like below:

Number - Version	p	Total
20.0.18362-May 2019 update	p-3	5
20.0.18363-June 2019 update	p-2	3
20.0.19363-version 22H1	p+1	13
20.0.19362- version 23H2	p+2	52
20.0.19602-June 2019 update	p-1	4
20.0.20000-update	p	83
Greater than 20.0.20000-update	p+	9

Requirement is to merge two rows where in " Number - Version" column (these are two separate column, have concatenated them) contains H1 and H2 and it should come in one row and in column 'p' it should be '20p+' :

 “20p+”  will show the aggregated values of p+1 & p+2 .

Expected:

Number - Version	p	Total
20.0.18362-May 2019 update	p-3	5
20.0.18363-June 2019 update	p-2	3
20.0.19363-version 22H2, 20.0.19362- version 23H2	20p+	65
20.0.19602-June 2019 update	p-1	4
20.0.20000-update	p	83
Greater than 20.0.20000-update	p+	9

@vinieme12  can you please help me with this?

[BrunPierre]

@abc_18  something like the below.

Script

Temp:
LOAD If([Number - Version] like '*H1*' or [Number - Version] like '*H2*','20p+',p) as p,
     [Number - Version], 
     Total,   
     RowNo() AS RowNum

FROM
[https://community.qlik.com/t5/QlikView-App-Dev/How-to-merge-two-rows-data-in-a-single-row-in-straight-table/td-p/2004621]
(html, utf8, UserAgent is 'Mozilla/5.0', embedded labels, table is @1);

DataTable:
NoConcatenate
LOAD p,
Concat([Number - Version]&[Number - Version],', ',RowNum) as [Number - Version],
Sum(If([Number - Version] like '*H1*' or [Number - Version] like '*H2*',Total,Total)) as Total

Resident Temp
Group By p;

DROP Table Temp;

EXIT SCRIPT;

O/P

[abc_18]

Hi Pierre,

Thanks for your response.

only question I have is that in above table "Total" I am calculating at chart level and along with Total I have other 5,6 measures.

but can see In the script provided by you Total you are using at script level, I don't think I will be able to use the same.

[BrunPierre]

Hi @abc_18

It can also be used as a chart expression.