Hi all,
I have the need to upload data on a regular basis and do some manipulation/transformation on it. Can anyone advise the best way to do this? Should I use the built in data load wizard of 4.2.5 or should I look for/create my own plugin/procedure for uploading data into a collection or something?
The table will look like this:
Date (Date)
Name (Varchar)
Volume (Number)
Manual_Volume (Number)
The uploaded data will all have date, name and volume.
I need to upload all of this data and then before it's put into the table, I need to run through each row and check the date. If it's a saturday or sunday, I need to add the volume to the next working day where the task names match and set the manual_volume to that figure, and then set the weekend day's manual_volume to 0.
For example, these lines:
| Date | Name | Volume | Manual_Volume |
|---|
| 23/08/2014 | Task One | 200 | |
| 25/08/2014 | Task One | 3000 | |
| 25/08/2014 | Task Two | 2500 | |
Would become:
| Date | Name | Volume | Manual_Volume |
|---|
| 23/08/2014 | Task One | 200 | 0 |
| 25/08/2014 | Task One | 3000 | 3200 |
| 25/08/2014 | Task Two | 2500 | |
If there wasn't already a row with the same task on the monday, a new row would be created with volume = 0 and the manual volume set.
I hope that makes sense. Is this possible somehow? I'm not sure if it can be done using a transformation on the built-in data loader or whether I have to code something myself.
Many thanks!