look up and join with current date and day

[ankitpaul7]

Hi Guys,

Am quite new to alteryx.  I need suggestion on this-

 there is one id which is functional in (mon,tues) and there is another id which may be functional in (mon,wed). So i need to create a column which should match up the day in the other file ( where only date is there)  and if that Id is not mapped in that particular day  then there should be a flag.

In short i have day in one file and date in other file with a common key available. How should i create a look up in this?

Let me know if it is clear or not. 

Thanks in advance folks! :)

[ankitpaul7]

Refer this excel attached.

[danilang]

This latest sample data makes it much clearer.  This is like a work schedule.  A works Mon,Tue,Thu,Fri.  For all of A's records in table 2, you want to see a yes on the days when she is supposed to work and a no when she is not.  Is this correct?

I cleaned up the date for line 6 in sheet2 to match the format of the other ones.  

Dan

[ankitpaul7]

Yes.. Precisely.. can you share the flow?

Thanks for this.. Much appreciated

[danilang · Accepted solution]

Here you go

Dan

[Kenda]

Hi @ankitpaul7

I have attached a workflow that I think accomplished what you're looking for. 

The first Formula tool just cleans up that one messy date.

After the join, I simply added a Formula with the following expression:

iif(contains([Days per Week],substring(datetimeformat(DateTimeParse([Current date],"%Y-%m-%d"),"%A"),0,3)),"yes","no")

Basically what this is doing is changing your date field into an actual date (with the date time parse) then changing it to the day value (with the date time format) then only keeps the first three letters for that day value (with the substring) and lastly checks if your Days per Week field contains those three letters. If it does, it will put a value of yes and will put a value of no otherwise.

See the attached 2018.1 workflow. If you have a version prior to 2018.1, change the version number of the file by right clicking on the workflow and selecting Edit with Notepad++.

[ankitpaul7]

Thanks a lot Danilang! Thanks for such a prompt response. This really helps.

This community is great. Love it!

[ankitpaul7]

Thanks @Kenda .. your solution, I have not tried but the inputs are quite good. I will make a note of it. I might use the date time formula in future tasks.

@danilang - Solution was quite apt for me. Thanks for it.