Skip to main content
Announcements
Qlik Connect 2025: 3 days of full immersion in data, analytics, and AI. May 13-15 | Orlando, FL: Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
abc_18
Creator II
Creator II

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

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?

Labels (1)
3 Replies
BrunPierre
Partner - Master
Partner - Master

@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

BrunPierre_1-1668627853913.png

abc_18
Creator II
Creator II
Author

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
Partner - Master
Partner - Master

Hi @abc_18

It can also be used as a chart expression.