cancel
Showing results for 
Search instead for 
Did you mean: 

How to concatenate cells correctly

0 Kudos

Hi everyone,

a couple days ago I tried to concatenate various cells into one. Until now I thought it worked, but apparently it didn’t for all my data as I found an error. I realized that the second row of an export was wrong, even though the following 450 rows were all correct. I repeated the export twice but the error remained. I cannot allow for any mistakes and have to ask for your advice.

I used the following structure to concatenate my cells into one:

https://archive.sap.com/discussions/thread/3245018

In my case, I did not have [lines] and a [category]. Instead of those I have a [ticket_number] which can have one or more [part_numbers].

If I only drag the [ticket_number] and the [part_numbers] into my column in SAP BO, I will not receive my part numbers in the same cell but will receive a second row which will display again the same ticket number (also other dimensions would be the same) but the [part_numbers] object will show a different part number which is connected to the ticket.

The output structure looks like this:

I marked the first two bold as they have the same structure of the row which did not concatenate correctly. The position of numbers and letters and the overall size is the same as in my data.

In my case, the concatenated cell of the structure marked in bold showed as:

Generally, part numbers can consist of different character lengths and have numbers as well as letters in it.

I want my output to look like this:

Sorry for not copying the values but using a screenshot, my structure became unclear when copying it.

It is not important what order exists within the concatenated part numbers, only that they are in the same cell and separated by a , and a space character.

I have the following variables in my data, same as in the linked post earlier as I copied the way of doing it:

[Max Category] = Max([part_numbers]) In ([ticket_number])

[Concat Category] = [part_numbers] +", "+ Previous(Self)

[Max Concat Category] =[Concat Category] Where ([part_numbers]=[Max Category])

[concatenated_part_numbers]= If(IsNull(Previous([Max Concat Category]));Substr([Max Concat Category];1;Length([Max Concat Category])-2);Substr([Max Concat Category];1;Pos([Max Concat Category];Previous([Max Concat Category]))-3))

So [concatenated_part_numbers] will be included in the report.

Is this structure not possible with the sample data I have? If so, any other possible way of merging these cells would be appreciated.

Thank you in advance for your support.

Best regards,

Jonas

Tom_N8
Contributor
0 Kudos

Hi Jonas,

Can you re-attach your first two screenshots? They don't display properly in your post.

Regards,

Tom

0 Kudos

Hi, sorry for not adding the proper data. Here is an example of the data, I hope it shows correctly now. I slightly changed it from the original post. The one marked in bold does not function as planned. Interesting is, that it works for 449 rows, just not one. The value in bold did not work as intended.

Output structure before concatenatingTicket NrPart Numbers723494N0 907 107 J723494N0 907 107 D72350RR 712 96772351347 270 675 C72351123 456 78972351DBI 478 798 Q

So after implementing the the changes, I receive the following

Example output after concatenating723494N0 907 107 Jwrong72350RR 712 967correct72351347 270 675 C, 123 456 789, DBI 478 798 Qcorrect My desired result looks as follows

Desired output after concatenating723494N0 907 107 J, 4N0 907 107 D72350RR 712 96772351347 270 675 C, 123 456 789, DBI 478 798 Q

0 Kudos

screenshot-example.png... having trouble to edit.. here as a picture...

Accepted Solutions (0)

Answers (4)

Answers (4)

Tom_N8
Contributor
0 Kudos

Hi Jonas,

Thanks for clarifying yesterday. Your adopted solution works absolutely fine when I try to replicate your problem. Have you checked whether you may have trailing blanks in your ticket numbers. Just create a variable with =Length([ticket_number]) and pull it into your block. If you have trailing blanks in your ticket numbers then create another variable such as [Clean Ticket No.] =RightTrim([Ticket No.]) and replace [ticket_number] with [Clean Ticket No.] in your [Max Category] variable. Place the [Clean Ticket No.] and [concatenated_part_numbers] in your table block.

In the screenshot above you can clearly see this works fine (table on top). The other two tables show the problem including a ticket with trailing blanks and with the described solution on the right.

Regards,

Tom

mhmohammed
Active Contributor
0 Kudos

Hi Jonas,

Here is a blog for it.
https://blogs.sap.com/2015/08/15/string-aggregation-on-webi-report-level/

Thanks,
Mahboob Mohammed

ayman_salem
Active Contributor
0 Kudos

I see the problem that max(part_number) for Ticket 72349 is row1 (.... J ), so it has no Previous(Self) and you only take into account the [Max Category] which is max (part_numbers)

[Max Category] = Max([part_numbers]) In ([ticket_number])

[Concat Category] = [part_numbers] +", "+ Previous(Self)

[Max Concat Category] =[Concat Category] Where ([part_numbers]=[Max Category])

....

Try to sort the table by part_number and ticket_number. or better your query with ORDER BY

mhmohammed
Active Contributor
0 Kudos

Hi Jonas,

The best way to address this issue is in the below blog, I've used it multiple times.

How to display column values in a single cell in Web Intelligence using Oracle database?

https://irfansworld.wordpress.com/2011/09/15/how-to-display-column-values-in-a-single-cell-in-web-in...

Thanks,
Mahboob Mohammed

0 Kudos

Hi Mohammed,

thank you for the reply. Unfortunately, I do not have access to the database itself.

I know that the best way would be to fix the issue in the database itself but currently I am limited to the use of SAP BO instruments.

Ill have a look into it though.

Regards,

Jonas