Options

[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

  • Options
    MartinLiebigMartinLiebig Administrator, Moderator, Employee, RapidMiner Certified Analyst, RapidMiner Certified Expert, University Professor Posts: 3,507 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
  • Options
    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!
  • Options
    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.