Okay, I figured out a way to do what I want on QlikSense. Added here in case there is anyone else who wants to create a macro in QLikSense for transforming tables. It is a multi-step process.
1) Create a data layer. In my case, I have a table which contains physical actions a vessel at sea can execute. To interpolate positions on a globe, all I needed was the actions with position and temporal data. So in a full reload, I load partial data from this table into a temporary table and then use the STORE command to place this data in a QVD file. During a partial reload, I then pull data from this QVD for calculations.
2) Execute macro using Vizlib. Previously I had used VizLib slider to set the "now" time for display, but this doesn't have macro execution. I found the better method was to use VizLib Input Form. A slider is available as an input. So I made a form with a slider, and coded the slider the same way as the previous one, where the min and max were based on the Max() and Min() times from the above table, and default being the variable vslider_time that will be set next. Set the VlizLib Writeback settings to write to a serverless QVD file.
3) Now that code can be executed, within the VizLib Writeback, set the actionable variable but modding the code:
temp1:
REPLACE LOAD
slider_time
From "lib://AppExports/update_slider_time.qvd" (qvd, utf8);
LET vslider_time = peek('slider_time', -1, 'temp1');
Then, at the end of this code, call a subroutine and pass the variable
CALL iterate_position(vslider_time);
4) Now the subroutine can chew on the data and save positions for everything. I am still working on this, but this code does an okay job:
sub iterate_position(vslider_time)
TRACE start of iterate_position subroutine;
[iterated_position]:
REPLACE LOAD
*
Inline [
'units_id','iterated_position.lat','iterated_position.lon'];
[temp_actions]:
LOAD
actions_id,
units_id,
latitude,
longitude,
game_time
FROM [$(vQVDPath)temp_actions.QVD] (qvd);
LET NumRows=NoOfRows('temp_actions'); //Used to set the number of iterations to read the table
SET IsEmpty=If(Isnull($1),'Yes','No'); //Used to determine if a Field is Null
FOR i=0 to $(NumRows)-1
LET vmaxtime = 45620; //Max(game_time);
LET vmintime = 45520; //Min(game_time);
LET vunits_id=Peek('units_id',$(i),'temp_actions');
FOR j=i to $(NumRows)-1
LET vunits_id_inner=Peek('units_id',$(j),'temp_actions');
IF vunits_id = vunits_id_inner then
LET vgame_time=Peek('game_time',$(j),'temp_actions');
LET vlat=Peek('latitude',$(j),'temp_actions');
LET vlon=Peek('longitude',$(j),'temp_actions');
//check to see if the game_time on this row is nearer to slider_time; iteratively should end up with time brackets
IF vgame_time >= $(vslider_time) and vgame_time <= vmaxtime then
LET vmaxtime = vgame_time;
LET vlatmax = vlat;
LET vlonmax = vlon;
END IF;
IF vgame_time <= $(vslider_time) and vgame_time >= vmintime then
LET vmintime = vgame_time;
LET vlatmin = vlat;
LET vlonmin = vlon;
END IF;
// Warning: This is a STRAIGHT interpolation. Replace this with a Vincenty algorithm to make it accurate over long distances!
LET iterated_lat = ((vslider_time - vmintime) / (vmaxtime - vmintime)) * (vlatmax - vlatmin) + vlatmin;
LET iterated_lon = ((vslider_time - vmintime) / (vmaxtime - vmintime)) * (vlonmax - vlonmin) + vlonmin;
END IF;
NEXT j;
[iterated_position]:
ADD ONLY LOAD *
Inline [
'units_id','iterated_position.lat','iterated_position.lon'
$(vunits_id), $(iterated_lat), $(iterated_lon)];
//we should now have iterated through the table only finding ONE unit with time bracketed between the two nearest points. (These points might be vmaxtime or vmintime)
NEXT i;
If IsPartialReload() then
STORE iterated_position into $(vQVDPath)iterated_position.qvd (qvd);
End If
end sub
5) And finally, in the visualization I use the interpolated positions in my map, and they can move. It still isn't quote as fast as I would like but a good partial solution.
