Alteryx Designer Desktop Knowledge Base

Error: "Oracle driver: You are not licensed to use this DataDirect Technologies product" when connecting to Oracle in Designer.

Cannot update passwords in the Input Data or the Output Data tool.

Error - Unable to find the dll: "telapi.dll", when using the Teradata Bulk Loader option in the Output Data tool.

Connecting to Hadoop HDFS/Hive/Impala/Spark with Alteryx Designer.

SPSS Output   Overview   When working with SPSS, values can have both a Text label and a numeric representation of the categories (equivalent of string factors in R). Columns can also have an encoded name ex. Q_1 and a longer descriptive name that maps Q_1 to the original question that was asked (closest thing in R is the data frame attribute on the column).   Alteryx reads .sav files and loads either the numeric representation or the textual representation of values based on the user’s selection. It also reads the variable labels into the Alteryx Field Description.  When writing .sav output, Alteryx will write either the text or the numeric values (depending on what was used in the workflow) as well as the SPSS variable labels which were displayed in the description field. However sometimes to maintain the integrity of the whole SPSS file, clients will want the value labels, value levels, and variable labels to appear in the output file. For these cases, using the Alteryx tools and a few lines of R code (while leveraging the thousands of R packages on CRAN) wrapped in a macro gives us the needed functionality. Attached is a macro which will write the data, variable & value labels back into SPSS.     Macro Process In this section, we will explain the R code logic that is specific to this macro. You can get an introduction to writing custom R code in Alteryx here.      Before we can do anything, we will need to pass the data to the tools inside the macro (more details on macros here). The raw numeric data should be connected to the D input of the macro. In addition, and because the data frames created in R don’t contain the Field Description data, we need to pass Field Description values to the M input (M for Metadata) of the macro. We’re using the Field Info Tool to extract the description into the rows and send it to the macro. With that done we can now look inside the macro.   Inside the Macro       Inside the macro, we are using the R Tool to contain the main functionality. We connect it to the Interface tools (Macro Inputs, File Browse, Action Tool) to get the data, metadata, and output file path from the user. Finally, we’re using the API tool to pass a message back to the user in the Alteryx Execution Messages.   The R Tool contains the code to properly format the input data and write it out to the .sav file. The majority of the work is already done for us in the ‘sjmisc' package from CRAN (R users know that most of the time they can find a package that does what they want). This package, among other features, implements reading and writing .sav files with both variable and value labels. We will first check if the package is not already installed and we’ll attempt to install it.   This workflow installs the additional sjmisc package. To avoid package version and dependency issues, it is possible to use Microsoft R Client as the base R. More details here.   if(!require(sjmisc)){ install.packages("sjmisc") require(sjmisc) }   If you would like to install the package separately you can use the R install packages App from the Alteryx Gallery.   filePath <- "c:\\temp\\outputRAlteryx.sav" inputData <- read.Alteryx("#1", mode="data.frame") ColumnLabels <- as.vector(read.Alteryx("#2", mode="data.frame")$Description)   Within the R code we also define a static ‘filepath ‘ pointing to where the output data should be written. The Action Tool that we previously mentioned will update this filepath to the one chosen by the user while at the same time correctly escaping the backslashes to work in R.   inputData <- read.Alteryx("#1", mode="data.frame") ColumnLabels <- as.vector(read.Alteryx("#2", mode="data.frame")$Description)   We then read the data that we pass to the macro from input ‘#1’ into an R data frame. In this case we are depending on R’s default behavior of transforming text to factors to get the Value encodings for all columns ex Male(1), Female(2). In addition, we read the metadata from input ‘#2’ into R. The sjmisc function, set_label, that applies the variable names to the data frame expects the variable names to be passed as an object of type vector. To make it work, we have to convert the Description column of the data frame we’re reading in into a vector with the as.vector() base R function. For more details about ‘sjmisc’, you can find the documentation here.   labeledData <- sjmisc::set_label(inputData,ColumnLabels) sjmisc::write_spss(labeledData,filePath)   Finally we label inputData with the labels we just created and we store the result in the labeledData dataframe and then write it to the user’s filepath using the sjmisc’s write_spss function.   MessageOut <- paste("file written to: ",filePath) names(MessageOut) <- "Output File Path" write.Alteryx(MessageOut, 1)   We also pass the filepath as a message to the R Tool output to be displayed to the user.     Edit: It was brought to our attention that the macro has an issue writing out text columns that are longer than 120 characters. Unfortunately this is a defect in the underlying R package. As a workaround for now, the macro was modified to trim all text fields to 120 characters. Please keep this in mind when writing out data.   Mandatory Note: This macro and sample code were developed by the authors as a proof of concept to show what's possible. This is not a production-ready macro and is not supported by Alteryx. Do ask questions on this thread - BUT use at your own risk!   WriteSPSSWithLabels_sjlabelled.yxzp has been updated from using the R package sjmisc because the set_label command has been deprecated from sjmisc and is now in sjlabelled.