Due to recent updates, all users are required to create an Altair One account to login to the RapidMiner community. Click the Register button to create your account using the same email that you have previously used to login to the RapidMiner community. This will ensure that any previously created content will be synced to your Altair One account. Once you login, you will be asked to provide a username that identifies you to other Community users. Email us at Community with questions.

[SOLVED] How to create a "real table" from a "virtual table"?

MacPhotoBikerMacPhotoBiker Member Posts: 60 Contributor II
in Help
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:                                                  
client_idrecordset_id column_header value
1 1000 NameJohn
1 1000 City Toronto
1 1000 Province ON
1 1001 NameMichael
1 1001 City Vancouver
1 1001 Province BC
2 1002 Name Rene
2 1002 CityMontreal
2 1002Province QC
In this layout, I can not access the information properly, so I'm trying to create a "classic" table, which should look like this:                                                                                                                                
client_id recordset_id NameCity Province
1 1000John Toronto ON
11001 MichaelVancouver BC
2 1002Rene Montreal QC
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!!

Answers

  • MartinLiebigMartinLiebig Administrator, Moderator, Employee, RapidMiner Certified Analyst, RapidMiner Certified Expert, University Professor Posts: 3,529 RM Data Scientist
    Hi,

    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
    - Sr. Director Data Solutions, Altair RapidMiner -
    Dortmund, Germany
  • MacPhotoBikerMacPhotoBiker Member Posts: 60 Contributor II
    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!
  • MacPhotoBikerMacPhotoBiker Member Posts: 60 Contributor II
    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 :)
Sign In or Register to comment.