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! :)

[Kenda]

Hi @ankitpaul7!

I'm not sure I understand your problem fully so if you could provide a sample of what your data looks like now and what you want it to look like after, that would be very helpful.

From what it sounds like, though, you should try the Join tool (in the preparation category). Use your id field to join on. The records that have the same id from both input files will come out the J output. The ones that don't have a common id between the two inputs will fall out the L or R. From there, you can add a formula with a flag then Union the records back or something. 

Hope this helps!

[danilang]

Hi

I'm not quite sure what you're trying to accomplish

Can you post some sample data?  Even just in tables would be enough. ie.

mon	A
tues	B
wed	C

With samples of both of your input tables, we should be able to come up with a solution

Thanks

Dan

[ankitpaul7]

Sorry Guys. I have updated two files separately. I need to link both these files.. Here if id is present in mon and  current date is (dd/mm/yyyy) then there should be a flag 'yes' in file2.csv. If not then  flag 'no'.

[Kenda]

@ankitpaul7 It looks like you can accomplish what you're wanting in the way I described in my previous post. Is this what you're looking for? If not, please explain.

To add your flag field, simply add a Formula tool and create a field with a string "yes" from the J output and "no" from the R output. If you only want certain fields appearing in your output, use a Select tool to get rid of some fields (or use the select features within the Join tool).

[Kenda]

@ankitpaul7 After reading your most recent post, it sounds like you want to check the joined records if any of the dates are the current date. To accomplish this, just add a Formula from the J output of your Join with the following expression:

iif(DateTimeToday()=[date],"yes","no")

This will add a flag field that says "yes" if the joined date is today and "no" otherwise.

Let us know if this is what you're looking for.

[danilang]

@Kenda  Interesting how we can both read the same text and interpret it differently

@ankitpaul7 I interpreted your explanation as if you need to match the day name form table 1, with the corresponding day of the date in table 2.  If this is so then

 

By the way, in table 2 you had duplicate values in the ID column.  I changed those to be unique

Dan

[ankitpaul7]

@Barnesk - In this post ,I have added two tabs which are two different files. I sincerely thanks for the earlier post also.. but i want an id to be match with day and date .. Please refer the file attached. It may be more clear

@danilang - almost what i require ..thanks. but id may be duplicate in nature.. as for eg. this id is functional in both the days ( mon,wed, thurs). Please refer the file attached. It may be more clear.