Ability to recall a column in the previous or next row. Something like: previous (ColumnName 3) or next (ColumnName 4). This would very useful to avoid going back and forth to Excel. Thanks, Dave
Can you give me an example where this would be useful? Ideally a simple example input dataset and what you want to transform it into.
Assuming I understand you correctly, you can already do this quite easily using 2 Total transforms and a Javascript transform to difference them. See attached.
running-total.transform (1.9 KB)
The solution provided works fine if all records are for the same customer. The goal is to get a running balance by customer (sorted by Customer then Date). So the solution provided does not reset the balance to zero when switching to a new customer. See attached screenshot for clarity and revised Transform.running-total revised by DRV.transform (2.2 KB)
Ok, that is more tricky.
You could use a PIvot or Unique to total by user (multiplying one of the columns by -1 first and adding them) but it would’t show you a running total.
You could Filter by user and show a running total, but you would need to know the ids of all the users first.
You can do a Spread to put one user per column, but you would need to add a separate Total column for each user. See:
running-total2.transform (3.0 KB)
This is something that will hopefully be doable with a more advanced Javascript transform, but that is unlikely in the next couple of weeks.
I would appreciate anything you can do. I would use this transform on a daily basis. I can get by over the next few weeks without it so do not feel rushed. Please let me know if I can help in any way.
Thanks,
Dave
1 Like
A less ambitious way of satisfying Vann’s change might be allowing JavaScript to access a global variable. That way, he could get a running total by adding to the global variable. This allows many other transformations as well.
A further small change, associating a global variable for each column, would allow still more common transformations, such as justifying text … etc.
@Titus I would like to have an advanced version of the Javascript transform that allow you to reference the entire table of values (rather than just one row at a time). Then you can write all sorts of custom transforms.
Even better. I thought a more modest implementation might fit in with your time constraints. That’s all.
Great to hear you are working on the best solution.
I hope to start on this and a few other features in the next few weeks.
@Vann This would be a simple way to solve your problem, wouldn’t it?
I need to think a bit more about the field names and tooltips, so that it isn’t confusing for people who just want a single running total.
1 Like
Yes, this should work. I will give it a go.
Give me a few hours to create a snapshot release.
@Vann Here is a snapshot release for Windows that has the extra By option in the Total transform. Let me know if it works ok for you.
https://www.easydatatransform.com/downloads/EasyDataTransform_1_13_2_shapshot_5.exe
This functionality worked flawlessly. I was able to achieve approximately 90% of my desired result. I still needed to go to Excel to complete the job with references to other rows (ie Balances for a Customer Record). It will be great when the new referencing other table values will be complete. Good Job.
Thanks,
Dave
1 Like
As a Mac user, I’m not feeling the love on this latest powerful update. 
@DanFeliciano This was a very specific snapshot aimed at @Vann. There will be a Mac snapshot soon. ;0)
Here is another example of why I would like to have the ability to reference columns in previous or next rows. This is a bit complicated but goal is try to reconstruct the original route number. After the accounts are closed the route number field is populated with a meaningless value such as CLOSED123456. Each Service Address should have only one route number so in Excel in Column B the value is calculated based on values of the previous row’s cells. Because this is a major data conversion that will be tested several times, I would like to do it in EDT. See Screenshot and attached csv file. Any help on how to accomplish in ET would be appreciated.
Thanks,
Dave
Route number problem to EDT Forum.csv (87.6 KB)
Dave, check out the attached transform,
I imported the data
I created two indexes using Row Num Transform; one index was offset
I joined the offset files to return a data set with current row and previous row data
Then I wrote a javascript.
@Vann I think the result you want is in the last column
Dave Transform.transform (4.0 KB)
1 Like