R code non parsed well

Hi, please see attached macro.

Practically what I want to achieve is to read dates in one table and compare them to intervals in the other table. Make a join based on this.

I found the solution in R here: https://stackoverflow.com/questions/41132081/find-which-interval-row-in-a-data-frame-that-each-element-of-a-vector-belongs-in

I works well in R studio but not in Alteryx.

Error: R (7): Error in grep(regexp, the.map$names, ignore.case = TRUE, perl = (length(regions) < :
Info: R (7): invalid regular expression '(^~)|(^.x %[]% data.frame(intervals[, c("start", "end")]))'
Info: R (7): Calls: %>% -> eval -> eval -> map -> map.poly -> grep
Info: R (7): In addition: Warning message:
Info: R (7): In grep(regexp, the.map$names, ignore.case = TRUE, perl = (length(regions) < :
Info: R (7): PCRE pattern compilation error
Info: R (7): 'unmatched parentheses'
Info: R (7): at ')'
Error: R (7): Execution halted

Any idea how to interpret/fix this?

Thank you,

VLookup Join.yxmc

R Tool

Accepted answers

florini

Thanks guys, at the end I took a different R code approach, I used foverlaps, see below the code. Hope this helps someone one day.

I compared this to 'Alteryx way' of adding one line for each day of the period and... Alteryx way was 3x faster in my case... I assume if I grow the number of intervals and their span, the process might reverse...

#require(data.table)
library(data.table)

access = read.Alteryx("#1", mode="data.frame")
suborders = read.Alteryx("#2", mode="data.frame")

access <-na.omit(access, cols="accesstime_out")
suborders <-na.omit(suborders, cold="startDate", "endDate")

access$accesstime_out2 <- access$accesstime_out

access$accesstime_out <- as.POSIXct(access$accesstime_out)
access$accesstime_out2 <- as.POSIXct(access$accesstime_out2)
suborders$startDate <- as.POSIXct(suborders$startDate)
suborders$endDate <- as.POSIXct(suborders$endDate)

x = data.table(access)
y= data.table(suborders)

setkey(y, cid, startDate, endDate)
w <- foverlaps(x, y, by.x=c("custid", "accesstime_out", "accesstime_out2"),
type="within", mult = "all")

df <- data.frame(w)
df[] <- lapply(df, as.character)

write.Alteryx(df)

All comments

Philip

Not sure about the R code, but you could do it with only Alteryx:

VLookup Join.png

VLookup Join.yxmc

florini

Thanks Philip, challenge is this only works for small datasets, but I want a script that can be applied to larger data sets...

The native Alteryx way of doing it, using rows generate is also possible but that one also requires millions of records at least... which cannot be usable if the data periods grow a lot...

Philip

This is the almost correct code:

You need the tidyverse package for the map function.

You need the intrval package for the %[]% function.

It still gives an error I'm tracking down.

Error: R (7): Error in `mostattributes<-`(`*tmp*`, value = attributes(elts[[1L]])) :
Info: R (7): adding class "factor" to an invalid object
Info: R (7): Calls: %>% ... .intrval -> .get_intrval -> pmin -> mostattributes<-

library(tidyverse)
library(intrval) 

elements = read.Alteryx("#2", mode="list")
intervals = read.Alteryx("#1", mode="data.frame")

phases_for_elements <- 
  map(elements, ~.x %[]% data.frame(intervals[, c('start', 'end')])) %>% 
  map(., ~unlist(intervals[.x, 'oid'])) 

result <- unlist(phases_for_elements, recursive = TRUE, use.names = TRUE)

final <- data.frame(lu,result)



write.Alteryx(final)

Philip

i am interested how the R function performs versus the Alteryx method. I ran 10 million records through the Alteryx on my i5 laptop in about 3 minutes.

Philip

i am interested how the R function performs versus the Alteryx method. I ran 10 million records through the Alteryx on my i5 laptop in about 3 minutes.

VLookup Join (1).yxmc

florini

Right, I installed the packages but did not called them anymore. I un-commented the lines, all worked well... now I get the same error as you do

Thanks for this observation...

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335