This content has been marked as final. Show 8 replies
You can do this with an import script. You will need to parse out the multiple account values so that they any additional values over and above the intial account value are added to the current work table at the import stage.1 person found this helpful
Thanks for confirming this as a possibility. I understand to some extent how to write the import script you describe - I have several that parse sql views and do other sorts of things. But...
Are you saying that I need to read in and parse the text file entirely through the import script, similar to the way I do a sql view, except read line by line instead of record by record? I'd prefer not to got completely custom on this and just manage the added data column(s) using script.
I understand if you don't want to post specific code, but I'm a bit vague on what you're saying above. Perhaps a little pseudo code and the specific (data pump, integration?) script type and API objects I would need to use would be very helpful.
It can be done in an import data pump script. You would need the following step sequence in your import script1 person found this helpful
1) Get name of the current FDM worktable (RES.PstrWorkTable)
2) Create a recordset based on that table - look at Data Access class of the DW API component for recordset methods
3) For Each additional account you want to load a value for you will have to append a new record to the recordset, populate all the fields you require (the original account/amount combo specified in the import format will load as per normal process). There is a scripting section in the FDM Admin guide which lists all the worktable fields.
4) Call the update method on the recordset to feed the new data rows into the worktable.
This process will execute for every line of data in your original load file. Hope that helps you make a start.
Awesome SH, Thanks.
I did actually try the datapump approach and your thoughts agree with what I was thinking.
The doc is a bit thin on the following, would you validate or correct me here?:
I create a new datapump script "MyDataPump",
The RES object (containing properties such as location key(PlngLocKey) and category key(PlngCatkey)) is valid in the context of the MyDataPump function
The DW object from which I will access the table [DW.DataAccess.farsTable(strWorkTableName)] is valid in the contet oof the MyDataPump Function
That all being ok, the one thing I still cannot figure out is where I get the name of the table where I'm loading the data (strWorkTableName)? Its passed as an arg in the Integration scripts, but not in the Datapump Script.
Aslo, since I'm updating the table with the data for this extra amount field myself, what value do I return from MyDataPump, and how is it used by the import process that calls the script (IOW since I inserted the record myself, I don't really want FDM to do anything with this field after the script is run.
You can get the worktable name from the following property:1 person found this helpful
What do I return from the data pump function?
I'm also not sure what field name to use on the import format screen in the bottom grid, do I create a second "Amount" field? I don't want to clobber the amount loaded in the first amount field.
Just attach the import script to one of your existing fields in your impoert format definition, you don't need to add another entry. As you are loading multiple account values maybe attaching it to that field would be logical. In terms of what you return from the scipt just return the field you assigned in the entry which should be the strField parameter passed to the script. Remember you want the import format to behave as normal for the original account, it will just be the addtional account/amount combinations you will be adding via use of the recordset in the script.
Perfect! Thank you.