Vlookup Macro

[superjesse]

Hello folks, I found a vlookup macro in this community and it is very close to what I need. I attached it to this message.

My challenge is this. I have date end only, and I need the macro to sort that way. 

Example data. The top data is the lookup. If my data has a trans date of 5/1/18 then I want the second row to be chosen. The date is less than 6/1/18, but greater than 3/1/18. The macro does it greater than the date, and I need it to do less than. I also have to group by first column. For each person I need this done (example only shows one example for a Person A)

The bottom data set shows what I want to see. I want to add the correct 'Person A #). I've been wracking my brain for hours trying to figure this out. The end result is I'm just adding a column to my data set with the 'Person A -(1 or 2 or 3..etc.)

Thank you so much in advance.

[superjesse]

I can't seem to attach the macro. It states:

The contents of the attachment doesn't match its file type.

[Thableaus · Accepted solution]

Hi @superjesse 

The Alteryx Community is a blast to create challenges and learn from them!

I loved your example and I dived into some R code to help you out on this. I modified some things to fit into your logic. I actually intend to use it in the future.

Check this workflow appended. To apply to different "Person" you need to create a batch macro using Control Parameter to change the Person field.

It's easy and I can explain to you if you can't do it.

The workflow is in version 2018.4 of Alteryx.

Cheers,

[superjesse]

Wow..awesome flow you sent. Of course, it works perfect, and when I try mine with it nothing works right.

My 'End Date' and 'Date' are both originally coming in as an actual date column. They are both in this format. 

2018-03-19

I tried different ways to make it work, and I can't get it to work.

I'm amazed by your workflow though, for I think I just have to figure out how to get my dates right.

[superjesse]

I cannot get the CorrDate to populate anything. It's just always null.

Thanks in advance for any help. I've spent hours trying to get this to work right, and I feel I'm so close to a solution.

[superjesse]

Hello there,

I changed my date to string, and then it worked fine. Perfect so far.

Now, you mentioned you can help me with a batch macro so I can do this per person. Please, if you have time I need that to complete this task. 

This workflow is so helpful. I need to dig into the R code more and learn this - seems very powerful. 

What would the change in code be if I wanted to do greater than a start date, for right now we figured out less than an end date. I can see so many scenarios where I could use this flow.

Current code:

lu = read.Alteryx("#1", mode="data.frame")
tx = read.Alteryx("#2", mode="data.frame")
lu$End.Date <- as.Date(lu$End.Date, format = "%m/%d/%Y")
tx$Date <- as.Date(tx$Date, format = "%m/%d/%Y")
tx$CorrDate <- sapply(tx$Date, function(x) min(ifelse(lu$End.Date > x, lu$End.Date, 10000000000)))
tx$CorrDate <- as.Date(tx$CorrDate, origin='1970-01-01')
write.Alteryx(tx, 1)
write.Alteryx(lu, 2)

WIth Start Date: I'm just not sure what to change for this.

lu = read.Alteryx("#1", mode="data.frame")
tx = read.Alteryx("#2", mode="data.frame")
lu$Start.Date <- as.Date(lu$Start.Date, format = "%m/%d/%Y")
tx$Date <- as.Date(tx$Date, format = "%m/%d/%Y")
tx$CorrDate <- sapply(tx$Date, function(x) min(ifelse(lu$Start.Date > x, lu$Start.Date, 10000000000)))
tx$CorrDate <- as.Date(tx$CorrDate, origin='1970-01-01')
write.Alteryx(tx, 1)
write.Alteryx(lu, 2)

Thanks for much for this. So much value here.

[Thableaus]

Hi @superjesse 

I'm going to work on the Batch Macro and I'll reply back soon.

Meanwhile, could you give an example of Input and desired Output using your new Start Date rule? Just so we're on the same page.

Great to hear you're also getting into some R code, I'm also very inexperienced in this area, but I'm so curious that I want to know the power of R in some cases where using regular Alteryx tools can make things complicated.

Cheers,   

[superjesse]

Oh man..thanks so much.

I'm attaching the same workflow, but adjusted the dates. Basically, when date is greater than Start Date.

'Person A - 1' should be for all dates from 1/1/18 - 06/01/18.

etc...

[Thableaus · Accepted solution]

Hi @superjesse 

It's me again.

I created the workflow with both StartDate and EndDate Macros.

Watch out for data types, field names and all details present. Remember also that date format in the macro is month/day/year. You can change in the workflow to another format or even alter the macro itself. 

The R tool depends much on metadata that you pass, so, again. Be aware of all details!

I hope this workflow solves the topic.

Cheers,