on 08-05-2010 2:43 PM
Hi
Using BODS, I'm trying to go from
+---+---+
| C | N |
+---+---+
| a | 1 |
| a | 2 |
| a | 3 |
| b | 2 |
| b | 4 |
+---+---+
to
+---+-------+
| C | f(N) |
+---+-------+
| a | 1,2,3 |
| b | 2,4 |
+---+-------+
Meaning, it should group using the column C and have as another column the concatenation of column N separated with commas.
In another word, I would like to do the opposite of this : [split a comma separated String into multiple rows|http://wiki.sdn.sap.com/wiki/pages/viewpage.action?pageId=49414399]
I tried but so far no luck in doing so. Have someone have an idea?
Regards
This message was moderated.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
I finally did it using the load_to_xml!
Pfiew that was some work.
Here is my solution: [https://wiki.sdn.sap.com/wiki/display/stage/Howtogroupfieldsintocommaseparated+values]
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi
I can't see how this can be done using the Reverse_Pivot transform.
I believe, correct me if I'm wrong, that you need to enumerate all axis values in order to create the corresponding pivoted columns.
I posted [a comment on the previous wiki entry|https://wiki.sdn.sap.com/wiki/pages/commentstab/viewcomments.action?pageId=198738912&focusedCommentId=198968299#comment-198968299] showing how I think it can be done using Reverse_Pivot but the limitation is that you should be aware of a maximum number of items to use this method. In my case I d'ont know it and the data source shows several hundreds of items, which would really be a boring thing to implements using this method.
I might have overlooked something because you are talking about discrete columns...
Regards
Guillaume
Giving more information, I tried to do the opposite of what's done in the quoted article above meaning make a nested table with the N values then convert it to an XML string.
So far it works but I can't find a way to convert the string outputted by the DataFlow I created using the load_to_xml function::
<?xml version="1.0" encoding="UTF-8"?>
<Nest>
<C>A</C>
<Ns><N>1</N>
</Ns>
<Ns><N>2</N>
</Ns>
<Ns><N>3</N>
</Ns>
</Nest>
Including carriage return, see the [DataFlow screenshot with result|http://hfr-rehost.net/http://self/pic/9d9a432ad1c2aed1414a24e8c7ef66cf98e9cef4.png]
to comma separated values : 1,2,3
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
The Reverse Pivot transform should do this for you.
If you do not have access to the DI transforms, another option is to use the Match transform to match on the first column then use the Best Record option to append the column 2 values to the master record.
You might also be able to do this via a SQL comand.
Paul
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
101 | |
13 | |
13 | |
11 | |
11 | |
7 | |
6 | |
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.