on 02-25-2019 5:51 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Jonas,
Here is a blog for it.
https://blogs.sap.com/2015/08/15/string-aggregation-on-webi-report-level/
Thanks,
Mahboob Mohammed
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Jonas,
The best way to address this issue is in the below blog, I've used it multiple times.
Thanks,
Mahboob Mohammed
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
84 | |
25 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.