I'm new to Talent Open Studio so sorry if this is a basic question. I have a csv file which I need to split into multiple related tables. The file contains a number of fields relating to a survey and if the user has provided their contact details they are stored in the same line. I want to load this into 2 tables, 1 which has the survey answers and a contact ID and the second has the contact details.
The problem I have currently is that I'm getting 1 row in the contact table for each survey. How can I structure my job so contacts are numbered sequentially with no gaps and that surveys are linked to the correct contact?
tMap may be used to split the records as desired. Adding a field filled with Numeric.sequence is a possible way to define a key you can use for both contact and related survey. Let me know if it's what you're looking for.
Hi Trf,
I'm using a tmap now but the problem is that I'm getting 4000 contact rows which are mostly empty because most of the surveys don't have contact information.
How do I limit those to only populated contacts and how can I then link them to a survey with a generated contact id?
You can filter using a tFilterRow or filter inside the tMap to exclude incomplete rows.
To join contact and survey, reuse the same value for both entities. If you use a Numeric.sequence, assign the value to a tMap variable the reuse this variable to populate all the desired output fields issued from the same original input row.
If you need more help, give us some details regarding the output db where these informations will be store and also may a sample data (input and expected result). If technical difficulties, share your job design.
Thanks for your suggestions. I was hoping to have different ID's for each type of data (surveys and contacts). I've created a sample input data file to give you an example of what I'm working with as input and 2 output files showing what I'd like to achieve
The input file has a row for each survey and if contact details are supplied they are included. The first output I'd like is the survey data with a SurveyID (Numeric.sequence). The second output is a list of contacts with a contactID. I'd also like the appropriate contact ID to appear in the survey data.
In my job the input is a csv file and the outputs are Database tables in a SQL Server database.
