Automating Experimental Lookup Table Signal Generation

[Joseph Parker]

Is there a way to automate generating signals in Seeq using a experimental lookup table? As of now for a particular use case, we are able to generate signals from an experimental lookup table where the table was constructed in excel first and then copied/pasted into formula within a workbench where it is then executed to generate the signal. However, the data within the experimental lookup table is from a separate spreadsheet that gets updated per day so if a specific key has it's value changed, we would have to manually go into the lookup table and change that corresponding value for the specific key and then re-execute. I know in data lab, spy.pull() can have a calculation argument or you can specify a formula to be applied. Any help or suggestions would be much appreciated. Thanks!

[John Brezovec]

To clarify, you're using the experimental_lookupTable function in formula, and want to update the values in the lookup table string programmatically with SPy?

[Joseph Parker]

Hey John, 

Thanks for the reply. Yes, that is correct. The updated values are from a spreadsheet in Sharepoint (typically gets updated every day).

[John Brezovec]

You can use the spy.push function to push metadata changes to existing items (as well as create new ones). Included in this metadata is the item's formula. You can programmatically write a new formula in python that incorporates the new lookup table values, and then push this back into Seeq. Here's a basic example of this workflow:

# search for item to update. The workbook ID is used to find an item scoped to a particular workbook
item = spy.search({'Name': 'Formula to Update'}, workbook='0EED58CF-1C10-62C0-BD3D-50D15376DF5E', all_properties=True)
# write the new formula programmatically (in your case a lookup table)
new_formula = '50'
# assign it back to the item that you pulled
item['Formula'] = new_formula
# push the change back into Seeq
spy.push(metadata=item, worksheet=None)

[Joseph Parker]

Thanks John, I will mess around in datalab and do some tests with the above workflow on some simple cases. I know the above is strictly just for modifying the formula and then pushing the formula back to Seeq but is possible to modify the formula in datalab and then apply back to the signal and re-generate all in datalab. I was thinking about adding a schedule as I will most likely have to use some API to pull in the spreadsheet from Sharepoint every day.

[John Brezovec]

Modifying the formula of a calculated signal/condition in Data Lab is the same as going into workbench and editing the formula by hand. So just as in workbench, once the formula updates, anytime you query that item (whether that's from workbench or SPy), it will perform the calculations based on the active formula. Let me know if that clarifies things.

[Joseph Parker]

Yes, that does. Thanks John for the help.