I’m wondering if it is currently possible to dynamically reference a column based on data from a different column. I’ve tried a few different ways but nothing has worked so far as I keep getting a reference error.
Example: I have calculated hours worked based on which week of the year the dates fall in. The columns are “9” “10”, “11”, etc. I have a script that calculates the current week of the year in a “Current Week” column.
I’m trying to take the current week # and reference the column with the same value. Ex. Current Week = 11 I want to take column “11” and calculate OT.
I’ve tried referencing like so:
var currentWeek = $(“Current Week”);
var hoursWorked = parseFloat($(currentWeek)) || 0;
Any help, or letting me know if this is even possible in the current state of the software would be appreciated
The Javascript transform is quite simplistic at present.
if you include in your code:
$(v1)
It will just take the value of the ‘v1’ column for each row and substitute the value for $(v1). And it will do that before the Javascript is evaluated.
I guess you could do something like this.
var v;
if ( currentweek == 1 )
v = $(v1);
else if ( currentweek == 2 )
v = $(v2);
etc
But it is a bit ugly.
It is on our wishlist to be able to use Javascript in a more dynamic way.
But are you sure it isn’t possible to do what you want with the standard transforms? It is surprising how much you can do without Javascript. Perhaps with a simple Lookup? Easy Data Transform can also work out the week of the year from a date using the Calculate transform. Can you provide a simple example input and the output you want for that input?
Thank you for this. Opens a lot of doors for me. I do use standard transforms to find the week of the year with hours from that week put in the week column per employee. I could probably just use some calculates especially with the new update. Code just makes more sense to me, I suppose.
I adopted this software with my current job as they use it for most things. I’m still learning it’s full potential. I’ll get there
Basically, I’m trying to calculate overtime worked by taking a pivot table I already use and calculating the over 40 hours off of it but only for the current week based on my week of the year calculate transforms.
Ex:
FROM
| Name | 1 | 2 | Current Week |
|-----------|--------|-------|--------------|
| Jane Doe | 48.24 | 62.7 | 2 |
| John Doe | 36.87 | 42 | 2 |
TO
| Name | 1 | 2 | Current Week | Overtime |
|-----------|--------|-------|--------------|----------|
| Jane Doe | 48.24 | 62.7 | 2 | 22.7 |
| John Doe | 36.87 | 42 | 2 | 2 |
