[SOLVED] How to create a "real table" from a "virtual table"?
Hi,
I'm trying to extract data from a database that stores information in "virtual tables".
This is how the data looks in the database:
In this layout, I can not access the information properly, so I'm trying to create a "classic" table, which should look like this:
Both tables contain exactly the same information, client 1 has two names assigned in this list, and client 2 (in green) has only one.
I somehow believe that one of the "Loop" operators might be able to achieve that, but I couldn't figure it out.
Could someone please help me to "transpose" the first table into the second one?
Thank you very much!!
I'm trying to extract data from a database that stores information in "virtual tables".
This is how the data looks in the database:
| client_id | recordset_id | column_header | value |
| 1 | 1000 | Name | John |
| 1 | 1000 | City | Toronto |
| 1 | 1000 | Province | ON |
| 1 | 1001 | Name | Michael |
| 1 | 1001 | City | Vancouver |
| 1 | 1001 | Province | BC |
| 2 | 1002 | Name | Rene |
| 2 | 1002 | City | Montreal |
| 2 | 1002 | Province | QC |
| client_id | recordset_id | Name | City | Province |
| 1 | 1000 | John | Toronto | ON |
| 1 | 1001 | Michael | Vancouver | BC |
| 2 | 1002 | Rene | Montreal | QC |
I somehow believe that one of the "Loop" operators might be able to achieve that, but I couldn't figure it out.
Could someone please help me to "transpose" the first table into the second one?
Thank you very much!!
Find more posts tagged with
Sort by:
1 - 3 of
31
Hi Martin,
thanks a lot for your answer! Maybe I'm not (yet) seeing the full scope of the pivot operator. As far as I see, it only allows for two variables (one for each line, one for the header). But what I would need the operator to create a one column for client, one for record ID, and then it needs to pivot whichever column header it finds.
Am I missing something, and the pivot operator can actually do this?
Thanks a again for your answer!
thanks a lot for your answer! Maybe I'm not (yet) seeing the full scope of the pivot operator. As far as I see, it only allows for two variables (one for each line, one for the header). But what I would need the operator to create a one column for client, one for record ID, and then it needs to pivot whichever column header it finds.
Am I missing something, and the pivot operator can actually do this?
Thanks a again for your answer!
Hi Martin,
thanks again for your reply!
You were right, the Pivot operator does the trick. My error was that I tried to do too many things in one single step, while the PIVOT operator only allows for 3 incoming variables.
I simply split the workflow now with the "Multiply" operator. In one stream, I find all unique clientID - recordID combinations, and in the other stream I pivot the table so that I get one line per recordID, with all possible different headers. Then I join the two streams, and I get exactly what I was looking for.
Thanks again for putting me on the right track!
RapidMiner rocks
thanks again for your reply!
You were right, the Pivot operator does the trick. My error was that I tried to do too many things in one single step, while the PIVOT operator only allows for 3 incoming variables.
I simply split the workflow now with the "Multiply" operator. In one stream, I find all unique clientID - recordID combinations, and in the other stream I pivot the table so that I get one line per recordID, with all possible different headers. Then I join the two streams, and I get exactly what I was looking for.
Thanks again for putting me on the right track!
RapidMiner rocks
the operator you search for is Pivot. Just have a look at the help of the operator. It is always a bit confusing but helpful.
Cheers,
Martin