How can I get data tables to be refreshed in Spotfire if I am using a data function to import data from Qualtrics?

Question

I am using the package qualtRics in TERR in Spotfire to pull in data directly from specific surveys in Qualtrics. The code I am using is:

registerApiKey(API.TOKEN = "xxxx") 
df <- getSurvey(surveyID = "xxxx", 
                root_url = "https://az1.qualtrics.com", verbose = TRUE)

My output df is a data table. I have 2 different surveys that I am pulling in 4 different times, 2 of those times I am unpivoting data, for a total of 4 data tables.
I want to be able to refresh this data. If I click Reload Data or try to refresh each table individually, nothing happens. I'm assuming I need to add some code that refreshes the data function (?), and I am trying to avoid replacing the data tables each time because, for 2 of those, I have to manually select which columns I am unpivoting (and I have 75+ columns).

Is there a way I can accomplish what I'm looking for? I am a beginner Spotfire/R user, so I am learning as I go!

Leake

How are you executing the function initially? You can set up an automatic refresh, but this is usually done when you are passing in a parameter. Great question BTW. Not sure why someone DV it. – Valdez

I execute the function by going to File > Add Data Tables > Add > Data Function... Then I select the function I created/saved, click OK, then OK again. For 2/4 tables, I will add Unpivot transformations, then click the final OK. In the Add Data Tables box/menu, for the Load method section, only Import data table is available to be clicked, if that informat helps. Thank you for the reply! – Leake

Since it seems like you won't know when the site has changed and want to run it adhoc, I think you could just add a button. Here's a good tutorial. datashoptalk.com/spotfire-data-functions-terr-basics – Valdez

Yes, that sounds like a perfect solution! I am reading this now - thank you for your help! I've been trying to scour the internet, but my search skills have failed me this time... :) – Leake

Hey no worries @Leake I stay pretty active on StackOverflow so just ask away! Niko and some others are awesome too. Cheers! – Valdez

Hi again~! I'm still a bit lost on how to actually refresh my data...I read through that tutorial, and I read a few other ones on the blog (e.g., datashoptalk.com/setting-up-a-button-function). I understand that my function is literally just pulling in data, so do I need to adjust my function, or should I be looking to do something else in addition to my function as is? /how do I account for unpivoted columns, caculcated columns, removed columns, etc? I'm sorry, I'm realizing that I should've included all of this information previously @_@... – Leake

Hey sorry @Leake I'll post an answer tomorrow when I'm back off my mobile. Cheers for now – Valdez

So, is the issue that you can trigger the function but this doesn't "reload" your data because you have to manually do the transformations? Do you expect the number of columns to change? – Valdez

If I use File > Reload Data or Edit > Data Table Properties > Refresh Data (with or without prompt), nothing happens. I know I can replace my data table with the same data function, but that means I would have to all the other stuff (transformations, calculations) manually again. As of right now, I don't expect the number of columns to change ^^;; – Leake

@Jen, one way to get around recreating un/pivot transformations is to create a "virtual data table". check out my explanation on this other question: https://mcmap.net/q/1628661/-better-way-to-refresh-imported-columns . maybe this can help you :) – Adulteress

Answer 1

I am not able to reply to your question as i dont have enough permission so keeping it as separate answer. Replacing table each time is good idea, By This you can fix your no of columns for pivoting/UnPivoting.

------R Code

row <- data.frame(Data_Points = nrows, Col1 = col1, Col2 = col2, YStart = y1, YEnd = y2)

row <- cbind(df, row)

return(row)

And also you can list your fix columns into DocumentProperty and loop it into your DataFunction.

Estas

Answer 2

Instead of using spotfire's pivot/unpivot, you can try doing the unpivot within the R code of the data function.

Teirtza

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center. – Attalanta