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

MacPhotoBiker

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_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

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	Name	City	Province
1	1000	John	Toronto	ON
1	1001	Michael	Vancouver	BC
2	1002	Rene	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!!

MartinLiebig

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

MacPhotoBiker

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!

MacPhotoBiker

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