Pages: [1]
  Print  
Author Topic: How to create a "real table" from a "virtual table  (Read 58 times)
MacPhotoBiker
Jr. Member
**
Posts: 59


WWW
« on: March 26, 2015, 11:16:53 PM »

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
11000 City Toronto
1 1000Province ON
1 1001 NameMichael
1 1001 City Vancouver
1 1001 Province BC
2 1002Name 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!!
Logged

MacPhotoBiker
Just do your Core Business. We do the rest.
Martin Schmitz
Global Moderator
Full Member
*****
Posts: 132


« Reply #1 on: March 27, 2015, 04:39:15 PM »

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
Logged
MacPhotoBiker
Jr. Member
**
Posts: 59


WWW
« Reply #2 on: March 27, 2015, 07:42:14 PM »

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!
Logged

MacPhotoBiker
Just do your Core Business. We do the rest.
MacPhotoBiker
Jr. Member
**
Posts: 59


WWW
« Reply #3 on: Today at 04:53:22 PM »

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 Smiley
Logged

MacPhotoBiker
Just do your Core Business. We do the rest.
Pages: [1]
  Print  
 
Jump to: