Reference Table to Map?

CloverDX Platform
1

Honestly, I’m not even sure what to use for a title. I am working on a data migration and have a problem I am not sure how to solve. A few things right out the gate –

1. I am by no means a data wizard and my knowledge of clover is pretty basic.
2. The version I am using the 4.3 Community

… I have a table with User records, or at least PART of a user record. In this table I have an ID column. In a separate table I have a set of records related to a user via the ID. So in the UsersMetadata table I have something like…

user_id meta_key meta_value
1 first_name andrew
1 last_name jardine
1 nickname aj
…
2 first_name chris
2 last_name chan
2 nickname jackie chan

etc.

I am trying to flatten my user records to be exported to a CSV so that I have

userId firstName lastName nickname
1 andrew jardine aj
2 chris chan jackie chan
etc.

There are literally about 50+ “meta fields” – some of them I no longer need. Right now the only solution I have come up with , which SUCKS … is to have a component that reads a meta key (eg. firstName) and then UPDATES a record in a mast table, then it reads the lastName, updates the table… but this is a pretty brutal way to do it and I am sure that there is something more clever and quick.

Can someone help me out?

2

Hi jardineworks,

From the way your data is set up, I would recommend that you use the Pivot component which you can read more about in our documentation here. You will want to output your data exactly as in your example using the DBInputTable component then use the Pivot (make sure that the output metadata is the same name as the meta_key field).

I have taken the liberty of attaching a sample graph with your use case.

3

Hi,

I forgot to mentioned that you can join your data based on the ID using our joiner components, such as ExtHashJoin.